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!