Getting the list of columns for a table

When writing up SQL or T-SQL code in SQL Server Management Studio or Query Analyzer, at times developers face the issue of writing down the column names in the table – a scenario could be if you are writing scripts to move data from one database schema to another or if you are writing conversion scripts. One can generate such a list by simple T-SQL code that queries the meta-data and pivots it, example:

SELECT TOP 1
STUFF((SELECT ‘, ‘ + T2.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS T2 WHERE T2.TABLE_NAME = T1.TABLE_NAME FOR XML PATH (”)), 1, 2,”) AS COL_NAMES
FROM INFORMATION_SCHEMA.COLUMNS AS T1
WHERE T1.TABLE_NAME = ‘CONCISE_DATA’
ORDER BY T1.ORDINAL_POSITION

And the output will be a comma separated list of the columns in that table. One can use a variable to store this information and put a while loop around the code to go through the list of the tables. That way, it is easier to build up a script – one can also put in special logic for certain columns if a certain type of transformation is required per table. If you look on our web-site, we had put a whitepaper together some time ago to deal with data porting issues between heterogeneous RDBMS and had demonstrated some of these techniques.

However, if you require to do something that is quick, then you can simply use the SSMS (or Query Analyzer) feature of dragging and dropping the column list. Example: In the image below, you can see the table VACCINE_HDR and the very first option is Columns.

Highlight that and then drag it to the query pane by holding on to your right click and you will get the columns copied over in a comma separated list as shown in the image below:

This is a quick way of getting the entire list of columns for a given table. Another scenario where you might need to do this is when you are trying to insert values into a table and need to provide an explicit value for the Identity column as well in which case, you need to specify the entire column list. This can save you some time.