I did not know about that first way: drag/drop the column folder. Neat and thanx!

Good One. I was aware of the first approach as I blogged about it here a while ago, but the second approach is a refresher for me :)

I always use the 2nd approach on large tables where I need a large subset of random columns, it is easier to untick the columns not required and copy the query. :)

Nice. Thanks

Great tips, both of them, but both are more complicated than necessary IMHO.
If your primary goal is to simply expand the column list of a table into a query without manually typing it all in, try this:

In SSMS 2k5:

1. right click the table in Object Explorer
2. Script Table as
3. SELECT To
4. "New Query Window" or "Clipboard"

Easy.

If you have 150 columns and you need say a random 72 of them in a specified order you can just tick the boxes for each column in the order you need in query builder. Using script SELECT TO would leave you with a massive cut and paste job.

Good point denzilwhite. I guess it all comes down to personal preference. I would rather do the text edit than a checkbox select and scroll, so whatever floats your boat. Using your example of 150 columns, the columns that are to be excluded can be commented out and the script saved. If you miss one column using the checkbox method, you need to start the process over again to find the missing column. That's just my personal preference, plus, I don't mind typing, so perhaps I'm the exception ;-)

If you miss one column you just type or drag it in :) I did not say to leave it in query builder, just to use it as a base for your query and then paste it back into query analyser.

I am definitely all for typing, as the more complex the query gets, the less likely query builder can support it. Or should I say display it graphically.

Anyway I guess this is now a conversation outside this discussion.

thanks for the post. really very helpful .

I just script the table as a SELECT from the table context menu. This gets me the SELECT in the format I like too.

Also, when dragging the columns in these are not wrapped in square braces so any column names that would need wrapping will need this to be done manually afterwards. Scripting the SELECT wraps the columns for you.

This tip not only for list the columns for single table, we can get the columns for CTE and Sub Query too.

Fox example, Our CTE or SubQuery returning multiple columns and we need to apply some calculations or conversions on particular columns,then definitely we need to type all the column names then we need to apply the changes on particular column.

But using this we can select the query and follow the TIP to get the list of columns.

Sample Query:

--EX:1
WITH CTE AS (
    SELECT SP.name ,  SC.text 
    FROM sysCOMMENTS SC 
    	 INNER JOIN sys.procedures SP ON SC.id = SP.object_id 
)
SELECT * FROM CTE 

--EX:2
SELECT * 
FROM (
    	SELECT SP.name ,  SC.text 
    	FROM sysCOMMENTS SC 
    		 INNER JOIN sys.procedures SP ON SC.id = SP.object_id 
     ) AS A