(Updated due to noeldr’s comment being a better option)
We all know that SELECT * is frowned upon when writing queries, but some tables have lots of columns that you don’t want to type out all the column names for. What’s the fastest way of getting a column list?
I used to script out the CREATE statement for the table, and then remove any extra square brackets and types. This had a lot of editing associated with it, which is relieved with Management Studio 2008 which provides the “SELECT TOP 1000 Rows” when right clicking on the table. This still provides square brackets, however. Other options were to put the results into text mode, and copy the header line. However, the fastest method I’ve found is using the Query Designer, which I’ve never had use for. Another option is to use the Query Designer, which I still have no use for.
To get a list of columns for a table, create a SELECT * statement for it:
USE AdventureWorks GO SELECT * FROM Production.Product
Next, highlight the SELECT statement, and press Control-Shift-Q or use the menu item “Design Query in Editor…” under the “Query” menu to bring up the Query Designer:
Select or deselect the columns you don’t want, and copy the list to the clipboard. That’s it!
Noeldr’s option, from the comments below is even better, and works in either Management Studio or Query Analyzer. Simply open the Object Explorer up to the table, expand the table so that the “Columns” node is visible, and drag this item from the Object Explorer into your query window. I love how you can work with a product for years and still find little tips like this!
Are there any other easy options I’ve missed, or should I always use square brackets when using column names? Let me know!