Get Column Lists script
This is a script like Column_Gitter by Rick Bolin (RickInMesa), it differs in the fact that it does not use cursors, it optionally adds the table name, it handles UDDT (User Defined Data Types) properly, and handles the new SQL Server 2000 datatypes.
Usage: Open this script in QA, Change QA to the desired Database, Edit the @TableName assignment for the desired table, Delete the Comment for the @T assignment to exclude table names from the output, Execute the script (F-5).
For the specified table, this script return 5 results: The Column names (SELECT list), the Column names with their datatypes (Table Creation List), the Column names with each assigned from an identically named variable (UPDATE SET list), a list of variables with the same names as the columns with their datatypes (DECLARE list), and a list of variables with the same names as the columns (VALUES or parameter list).
-- Get Column Lists script 7/20/2003
SET NOCOUNT ON
DECLARE @TableName varchar(128), @String varchar(8000), @T varchar(128)
-- Assign table name to output
SET @TableName = 'titles' -- From pubs database (has UDDT)
-- Include table in output
SET @T = @TableName+'.'
-- Delete comment to exclude table in output
--SET @T = ''
-- Column List
SELECT @String=ISNULL(@String+', ','')+@T+syscolumns.Name
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.Name = @TableName
ORDER BY syscolumns.colorder
SELECT @String AS [Column List]
-- Column List with Datatype
SET @String = NULL
SELECT @String = ISNULL(@String+', ','')+syscolumns.name+' '+systypes.name
+ CASE
-- Do not need more for UDDT (User Defined Data Types)
WHEN syscolumns.xtype <> systypes.xusertype THEN ''
-- Need Precision and Scale for decimal and numeric
WHEN syscolumns.xtype IN (106, 108) THEN '('+CONVERT(varchar,syscolumns.prec)+', '+CONVERT(varchar,syscolumns.scale)+')'
-- Need Size for varchar, char, nvarchar, and nchar
WHEN syscolumns.xtype IN (167, 175, 231, 239) THEN '('+CONVERT(varchar,syscolumns.prec)+')'
-- Do not need more for image, text, uniqueidentifier, tinyint, smallint, int, smalldatetime, real, money, datetime, float, sql_variant, ntext, bit, smallmoney, bigint, varbinary, binary, timestamp
--(34, 35, 36, 48, 52, 56, 58, 59, 60, 61, 62, 98, 99, 104, 122, 127, 165, 173, 189)
ELSE ''
END
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @TableName
ORDER BY syscolumns.colorder
SELECT @String AS [Column List with Datatype]
-- Column List with Variable
SET @String = NULL
SELECT @String=ISNULL(@String+', ','')+@T+syscolumns.Name+' = @'+syscolumns.Name
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.Name = @TableName
ORDER BY syscolumns.colorder
SELECT @String AS [Column List with Variable]
-- Variable List with Datatype
SET @String = NULL
SELECT @String = ISNULL(@String+', @','@')+syscolumns.name+' '+systypes.name
+ CASE
-- Do not need more for UDDT (User Defined Data Types)
WHEN syscolumns.xtype <> systypes.xusertype THEN ''
-- Need Precision and Scale for decimal and numeric
WHEN syscolumns.xtype IN (106, 108) THEN '('+CONVERT(varchar,syscolumns.prec)+', '+CONVERT(varchar,syscolumns.scale)+')'
-- Need Size for varchar, char, nvarchar, and nchar
WHEN syscolumns.xtype IN (167, 175, 231, 239) THEN '('+CONVERT(varchar,syscolumns.prec)+')'
-- Do not need more for image, text, uniqueidentifier, tinyint, smallint, int, smalldatetime, real, money, datetime, float, sql_variant, ntext, bit, smallmoney, bigint, varbinary, binary, timestamp
--(34, 35, 36, 48, 52, 56, 58, 59, 60, 61, 62, 98, 99, 104, 122, 127, 165, 173, 189)
ELSE ''
END
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @TableName
ORDER BY syscolumns.colorder
SELECT @String AS [Variable List with Datatype]
-- Variable List
SET @String = NULL
SELECT @String=ISNULL(@String+', @','@')+syscolumns.Name
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.Name = @TableName
ORDER BY syscolumns.colorder
SELECT @String AS [Variable List]
SET NOCOUNT OFF
GO 
