T-SQL, Cursors, FETCH INTO. How to use SELECT *

Question

I am building a one off query to iterate through a set of joined tables. The select statement is using "SELECT *". Since it's is a table with lots of fields, I don't want to specify each column as a variable. Is there a way to FETCH INTO an array or SET of some sort and just grab the values I want?

Answer 1

Even if there was, you wouldn't want to. Fetching extra fields is one of the most common causes of performance degredation in a SQL Server application. By doing so, you restrict the optimizer's ability to use indexes effectively.

How would you propose to "grab the values that you want" without specifying column names?

Answer 2

Apparently not:

INTO @variable_name[ ,...n]

"Allows data from the columns of a fetch to be placed into local variables.
Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list."

---

If you are looking to use cursors you may find better flexibility with CLR Stored procedures

Answer 3

Why do you need to use a cursor? Those are frowned upon in SQL Server scenarios - most of the time, they're unnecessary, and usually they're very slow and hurt performance.

What are you trying to accomplish with your cursor? Couldn't you do it in a set-based manner and use SQL Server's abilities to the max??

Marc

Answer 4

In 2k5 (SQL 2000 has different system objects), use the SQL metadata to quickly write queries with long lists of table/column names:

SELECT
    O.Name + '__' + C.Name + ' = ' + O.Name + '.' + C.Name + ','
FROM Sys.Objects O
JOIN Sys.Columns C
    ON C.Object_Id = O.Object_Id
WHERE O.Name IN ('Table1', 'Table1')
ORDER BY O.Name, C.Column_Id

I'm not sure exactly what you're trying to do with the result set, but this might be a decent start.

Also, you could declare variables as SQL_VARIANT and FETCH result sets into them like this:

DECLARE @Col1 SQL_VARIANT

SELECT @Col1 = Table1.Column1 FROM Table1

PRINT CONVERT(varchar(max), @Col1)

I'm not sure what that gains you though.

Answer 5

This is being suggested only for those that work with SQL daily, not specifically to answer this question.

The RedGate product SQL Prompt has the ability to expand wild-card characters into individual field names by using short-cut keys (Ctrl-B, Ctrl-W). This can save you alot of time and make it easier to remove a few fields from the query for those occasions where you do need most of them.

Answer 6

In SQL2005 you could also right-click the table an choose open table. Then you click the little SQL-Icon and you see the select statement, something like select * from YourTable. Clicking again on the !-icon the select * from will be expanded to the full fieldlist. Good luck.