Systems Engineering and RDBMS

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:

drag_drop_1

And then after dragging and dropping it in the query pane:

drag_drop_2

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 X.table_name,(
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.

Resources:

  • sp_MSForEachTable – here.
  • Another way to pivot – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: