Sep 022009
 

(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:

image

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!

  5 Responses to “Quick Tip #5: Get Column Names”

  1. Drag’n drop the “columns” Folder of the object explorer

  2. Thanks noeldr – I don’t think it could get faster than that! I’ve updated the post to include your method.

  3. Actually there is another method.

    On SSMS, go to Tools|Options|Query Results|SQL Server|Results to Text| select Output format = Comma delimited. I have this setting always on. This is a one-time setting.

    Next all you have to do to get the columns names is
    1) Hit CTRL T for text results
    and run your command.

    It is very simple and saves a lot of time for my developers.

  4. Hey Sankar

    Yes that seems to be even simpler method for getting the column lists easily. Thank you.

    Hi Jim,

    Most of us aren’t aware of these minute information. Its great that you blog those as well.
    Thank you Jim

  5. Highlight the name of the table in the query editor and press ALT + F1 (this runs sp_help , unless you have change your keyboard defaults). Then just copy the column names from the results list.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>