A very handy feature in SSMS
Posted by decipherinfosys on May 4, 2009
While writing some data migration scripts for a client, it was becoming cumbersome to write down the column names so I wrote up a script which generated all the column names per table in a comma separated fashion. I will show the script below but before that, let’s look at a feature in SSMS which one of my colleagues pointed out to me. In order to generate a comma separated list of the column names, one can simply use SSMS. Suppose, we want to pull out the column list for Person.Contact in the AdventureWorks database, you can open up the object explorer in SSMS, navigate to the section where the specifics are listed for Person.Contact, highlight the “Columns” and drag and drop it in the query pane to get all the column list separated by commas. Here is an example:
And then after dragging and dropping it in the query pane:
Cool. So, no typing of the columns and no need for having a script either if you want to do this on an adhoc basis for some of the tables. If however, you want to generate this for all the tables in a schema or for a sub-set of tables, then you need to have a script. Here is a simple way of generating it for all the tables in a schema:
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 = X.table_name
ORDER BY T1.ORDINAL_POSITION
) as Col_List
from information_schema.tables as X
where X.table_type = 'base table'
order by X.table_name
One can also make use of sp_MSForEachTable but since it is un-documented, it is better to stick with information_schema views.