I’ve been writing some insert and select SQL statements for some very wide tables. Microsoft’s Intellisense is normally a great help here, and can save quite a bit of typing. However some of the tables had a 100+ columns and the effort involved in typing those in names would have been large, even with Intellisense’s assistance. My other trusted technique of generating a create table script in Management Studio and modifying that would also have taken a lot of editing. As a developer my mind naturally wondered towards techniques for automating this process and I came up with three methods, though I’m sure there are many more.

First Method - Using Management Studio

By default SQL Server displays the results of a query as a grid. However by tweaking Management Studio’s settings the column headers, along with the data, can be displayed as a comma separated list. We’re not interested in the data but by generating an empty resultset we can create a comma separated list of column names, ready for copying into a query. To do this select the ‘Query Options...’ dialog from the Query menu. Select ‘Text’ under ‘Results’ in the left hand pane. In the Output Format dropdown select ‘Comma Delimited’ as shown in the screenshot below.
The final step is to actually set the results to display as text, by selecting ‘Query’ / ‘Results To’ from the menu and selecting the ‘Results To Text’ menu item. Once that’s done write a simple ‘select * from table’ query to display column headers in the Results pane, ready for copying and pasting, as below.

Second Method – Using Management Studio 2008

The next method only works with SQL Server 2008 Management Studio as it uses a new feature. Write a query to display the data in the table using the default grid layout, left click in the top left corner of the Results pane to select all the results, then right click and select the ‘Copy with Headers’ menu option. You can see that in the screenshot below.
Next fire up Excel and paste the results into a new spreadsheet. The column header and data are pasted in, with one table column per spreadsheet column. You can delete the data rows as we don’t need that, and then save the spreadsheet as a csv file. Open the csv file in notepad or similar to see the csv column headers. OK, I’ll admit that this technique isn’t hugely different from the previous one, but is an alternative which some may prefer.

Third Method – Write Some SQL Code

As a developer writing code generally seems more attractive than almost any other solution. There are a number of scripts available that will script up the csv column list, usually using information from the system tables. There is even a script available on this site which will also work with older versions of SQL Server. I won’t repeat the script here, but will just refer you to the link : Creating a comma separated list of all columns in a table