Tuesday 29 September 2009

TSQL : Comma Separated List of Columns

Like the title says, how to generate a comma separated list of columns for a given table...

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName  VARCHAR(100)
DECLARE @CommaSeparatedColumnList VARCHAR(MAX)

SET @SchemaName = 'myschema'
SET @TableName  = 'mytable'
SET @CommaSeparatedColumnList = ''

SELECT @CommaSeparatedColumnList = COALESCE(@CommaSeparatedColumnList + '[' + COLUMN_NAME + '],','')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName          
ORDER BY ORDINAL_POSITION
SET @CommaSeparatedColumnList = LEFT(@CommaSeparatedColumnList,LEN(@CommaSeparatedColumnList)-1)

SELECT @CommaSeparatedColumnList

No comments: