Technical Article

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 = ''

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating