Techniques for Creating a List of the Columns in a Table

Category : Blogs Published : August 6, 2012 User Rating : 4.5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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. How to create a comma separated list of columns 1
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. How to create a comma separated list of columns 2

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. How to create a comma separated list of columns 3
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
Link back to this article : https://www.sqlmatters.com/Articles/Techniques for Creating a List of the Columns in a Table.aspx

Keywords

CSV,column names


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered