Column_Gitter Part Deux
This script is similar to Column_Gitter. I noticed this script and wanted to present the same output without the use of cursors.
Both scripts work well and produce the same results.
This little utility comes in handy if you do a lot of SQL coding. Given a table name, it'll return four results: The names of the columns, the names with their datatypes, the list of columns with each one set to an identically-named variable, and a list of variables with the same names as the columns.
Greg
DECLARE @tblName VARCHAR(500)
DECLARE @Output VARCHAR(8000)
SET @OutPut = ''
SET @tblName = 'SUMM_ARKeyStatistics_History'
SELECT @OutPut = @Output + syscolumns.name + ' ' + UPPER(systypes.name) +
CASE
WHEN (syscolumns.xtype NOT IN (35, 36, 48, 52, 56, 58, 59, 60, 61, 104)
AND syscolumns.xtype IN (106, 108))
THEN '(' + CONVERT(VARCHAR, syscolumns.prec) + ',' + CONVERT(VARCHAR, syscolumns.scale) + '), '
WHEN (syscolumns.xtype NOT IN (35, 36, 48, 52, 56, 58, 59, 60, 61, 104)
AND syscolumns.xtype NOT IN (106, 108))
THEN '(' + CONVERT(VARCHAR, syscolumns.prec) + '), '
ELSE ', '
END
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @tblName
ORDER BY colorder ASC
SELECT @OutPut
SET @OutPut = ''
SELECT @OutPut = @Output + syscolumns.name + ', '
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @tblName
ORDER BY colorder ASC
SELECT @OutPut
SET @OutPut = ''
SELECT @OutPut = @Output + '@' + syscolumns.name + ', '
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @tblName
ORDER BY colorder ASC
SELECT @OutPut
SET @OutPut = ''
SELECT @OutPut = @Output + syscolumns.name + ' = ' + ' @' + syscolumns.name + ', '
FROM syscolumns
INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @tblName
ORDER BY colorder ASC
SELECT @OutPut
SET @OutPut = '' 
