how to print all the column names with in a table in one row from sys table without pivot

  • how to print all the column names with in a table in one row from sys table without pivot

    SELECT t.name AS table_name,

    c.name AS column_name

    FROM sys.tables AS t

    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

    ORDER BY table_name

    instead of printing in rows

    Tablename Column name

    Table1 column name1

    Table1 column name2

    Table1 column name3

    Like to print as

    Table1 , column name1, column name2,column name3

  • This?

    SELECT OutTab.TABLE_NAME,

    STUFF ( (

    SELECT ', '+InTab.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS InTab

    WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME

    ORDER BY InTab.ORDINAL_POSITION

    FOR XML PATH(''), TYPE

    ).value('.','VARCHAR(MAX)'

    ) , 1,1,SPACE(0)) AS Col_Names

    FROM INFORMATION_SCHEMA.COLUMNS OutTab

    GROUP BY OutTab.TABLE_NAME

  • thanks

  • ColdCoffee (3/2/2012)


    This?

    SELECT OutTab.TABLE_NAME,

    STUFF ( (

    SELECT ', '+InTab.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS InTab

    WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME

    ORDER BY InTab.ORDINAL_POSITION

    FOR XML PATH(''), TYPE

    ).value('.','VARCHAR(MAX)'

    ) , 1,1,SPACE(0)) AS Col_Names

    FROM INFORMATION_SCHEMA.COLUMNS OutTab

    GROUP BY OutTab.TABLE_NAME

    Is there a specific reason why you put in "space(0)" and not an empty string ''?



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • R.P.Rozema (3/4/2012)


    ColdCoffee (3/2/2012)


    This?

    SELECT OutTab.TABLE_NAME,

    STUFF ( (

    SELECT ', '+InTab.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS InTab

    WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME

    ORDER BY InTab.ORDINAL_POSITION

    FOR XML PATH(''), TYPE

    ).value('.','VARCHAR(MAX)'

    ) , 1,1,SPACE(0)) AS Col_Names

    FROM INFORMATION_SCHEMA.COLUMNS OutTab

    GROUP BY OutTab.TABLE_NAME

    Is there a specific reason why you put in "space(0)" and not an empty string ''?

    Just fancy stuff, no real reason 🙂

  • !

  • Is it Possible to Print the column list in different column

  • vijayangk (3/6/2012)


    Is it Possible to Print the column list in different column

    Not sure what you are asking. Could you explain this a little better, perhaps show us what you are looking for here?

  • As of now the result has 2 columns

    1st one has the table name and the second column has all the column names with comma separator

    TableName | col1,col2, col3,..... |

    My question: is it possible to load the each columns names in different columns

    TableName | col1 |col2 | col3 |

  • vijayangk (3/7/2012)


    As of now the result has 2 columns

    1st one has the table name and the second column has all the column names with comma separator

    TableName | col1,col2, col3,..... |

    My question: is it possible to load the each columns names in different columns

    TableName | col1 |col2 | col3 |

    Forget what you have been given already and read the following two articles:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

  • Just so you know, you need to pivot the data at some point. You could use a split function on the string, but you will find that you are pivoting that data to put it in a single row.

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

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