Get a CSV list of columns in a table

  • Comments posted to this topic are about the item Get a CSV list of columns in a table

  • Thank you for the post. However it appears to only work for tables in the 'dbo' schema and returns the columns in alphabetical order.

    The modified script below gives the user the option to but the schema name and also outputs the columns in the order within the table:

    DECLARE @TableName SYSNAME = 'OptionalSchemaName.tableName'

    DECLARE @Schema SYSNAME = LEFT(@TableName,NULLIF(CHARINDEX('.',@TableName),0)-1)

    SET @TableName = SUBSTRING(@TableName,ISNULL(LEN(@Schema)+2,1),128)

    SELECT

    REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]

    FROM sys.columns

    WHERE OBJECT_NAME(object_id) = @TableName

    and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')

    ORDER BY column_id

    FOR XML PATH('') )),1,1,'')),' ','')

    GO

  • Why all the complication with REPLACE(REVERSE(STUFF(REVERSE ...

    I typically do this to get a comma separated list:

    SELECT STUFF((SELECT ',' + name AS [data()]

    FROM sys.columns

    WHERE OBJECT_NAME(object_id) = @TableName

    ORDER BY column_id

    FOR XML PATH('')), 1, 1, '')

    Put in a leading comma in the list instead of a trailing comma, and then remove it after the list is created with the STUFF command.

    Tom

  • /*Another way to get the fields in comma separated list and store the returned string in a VARCHAR is:*/

    DECLARE @TableName SYSNAME = 'tablename'

    DECLARE @ColumnNames VARCHAR(MAX)

    SELECT @ColumnNames = COALESCE(@ColumnNames + ',','') + name

    FROM sys.columns

    WHERE OBJECT_NAME(object_id) = @TableName

    ORDER BY column_id

    SELECT @ColumnNames

    /* I use COALSCE frequently to concatenate multiple values to pass off to something else as a single variable.*/

  • Tom Bakerman (3/30/2015)


    Why all the complication with REPLACE(REVERSE(STUFF(REVERSE ...

    I typically do this to get a comma separated list:

    SELECT STUFF((SELECT ',' + name AS [data()]

    FROM sys.columns

    WHERE OBJECT_NAME(object_id) = @TableName

    ORDER BY column_id

    FOR XML PATH('')), 1, 1, '')

    Put in a leading comma in the list instead of a trailing comma, and then remove it after the list is created with the STUFF command.

    Tom

    Be careful with XML PATH:

    use tempdb;

    create table dbo.test_columns (id int, [this & that] varchar(100));

    declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

    SELECT STUFF((SELECT ',' + name AS [data()] FROM sys.columns

    --WHERE OBJECT_NAME(object_id) = @TableName -- assumes dbo schema

    WHERE object_id = object_id(@TableName) -- use object_id function including schema

    ORDER BY column_id

    FOR XML PATH('')), 1, 1, '')

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • brian.miller 15246 (3/30/2015)


    Thank you for the post. However it appears to only work for tables in the 'dbo' schema and returns the columns in alphabetical order.

    The modified script below gives the user the option to but the schema name and also outputs the columns in the order within the table:

    DECLARE @TableName SYSNAME = 'OptionalSchemaName.tableName'

    DECLARE @Schema SYSNAME = LEFT(@TableName,NULLIF(CHARINDEX('.',@TableName),0)-1)

    SET @TableName = SUBSTRING(@TableName,ISNULL(LEN(@Schema)+2,1),128)

    SELECT

    REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]

    FROM sys.columns

    WHERE OBJECT_NAME(object_id) = @TableName

    and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')

    ORDER BY column_id

    FOR XML PATH('') )),1,1,'')),' ','')

    GO

    No need to parse out the schema name...just use the object_id function to resolve your schema-qualified table reference to an object_id. See my earlier comment regarding the pitfalls of using XML PATH. Also see the later comments about not needing to use REVERSE.

    use tempdb;

    create table dbo.test_columns (id int, [this & that] varchar(100));

    declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

    SELECT REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]

    FROM sys.columns

    --WHERE OBJECT_NAME(object_id) = @TableName

    --and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')

    where object_id(@TableName) = object_id -- no need to parse schema, use object_id()

    ORDER BY column_id

    FOR XML PATH('') )),1,1,'')),' ','')

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Personally I like the "concatenated aggregate" method sqlservercentral 46394 showed however Erland pointed out some problems with it if you mess with the ORDER BY in specific ways, e.g. adding expressions or referencing columns that are not in the SELECT-column list. Therefore, use with caution.

    Here is the XML method that makes use of TYPE:

    use tempdb;

    create table dbo.test_columns (id int, [this & that] varchar(100));

    declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

    SELECT STUFF(

    (SELECT N',' + n

    FROM (SELECT

    name AS n

    FROM sys.columns s2

    WHERE object_id = object_id(@TableName)

    ) r

    FOR XML PATH(''),

    TYPE

    ).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS column_list;

    The query above results in the proper column names when the column names contain XML escapable characters:

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • That's pretty cool, thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply