Thursday, 4 July 2013

SQL how to print all the column names with in a table in one row


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

No comments: