It was the coldest night of the year a query with a lot of columns had me in tears…

Yesterday I was working with Jason so super big thank you to him for this script.  We were writing an insert statement and it had a lot of columns.  I was getting ready to script out the table when he showed me this little bit of code.  If you enter your database name and table name it will give you all your columns with commas.  You can even toss your alias in the query so you don’t have to spend a bunch of time adding it.

Declare @DBName as varchar(100)
Declare @tablename as varchar (100)

Set @DBName = 'MyDatabaseName'
Set @tablename = 'MyTableName'

Select T.TABLE_SCHEMA, T.TABLE_NAME
, Stuff(
(
Select ', ' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
Where T.TABLE_CATALOG=@DBName
and T.TABLE_NAME=@tablename

I hope you enjoy it too!

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

One thought on “It was the coldest night of the year a query with a lot of columns had me in tears…

  1. Nice query 🙂 I don’t know if you have tried it but if you drag the “columns” heading from object explorer into a query window it does the same thing.

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