How to get all columns name in SQL Server.

Deepak M

How can i get all tables name with its columns name in a table in SQL Server ?
For example

tablename columnsname
table1 column1,column2,column3...
table2 column1,column2,column3...
table3 column1,column2,column3...

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Accepted answer
Hey, check the below queries, this may help you.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp(
    sqlid INT IDENTITY,
    tablename NVARCHAR(100),
    columnsname NVARCHAR(MAX)
)
INSERT INTO #temp
  SELECT TABLE_NAME,
         COLUMN_NAME = STUFF(
         (
            SELECT ', ' + COLUMN_NAME
              FROM INFORMATION_SCHEMA.COLUMNS t2
             WHERE t2.TABLE_NAME = t1.TABLE_NAME
               AND TABLE_SCHEMA = 'dbo' FOR XML PATH('')
         ), 1, 1, '')
    FROM INFORMATION_SCHEMA.COLUMNS t1
   WHERE TABLE_SCHEMA = 'dbo'
GROUP BY TABLE_NAME
SELECT * FROM #temp
+2
Sachin Singh4y ago
You may be using older version of sql server for older version use this
SELECT TABLE_SCHEMA ,
   TABLE_NAME ,
  stuff( (
     select ','+COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS t1
     where t1.TABLE_SCHEMA=t.TABLE_SCHEMA and t1.TABLE_NAME=t.TABLE_NAME
      FOR XML PATH('')
   ),1,1,'') as ColumnNames

FROM   INFORMATION_SCHEMA.COLUMNS t
Group by TABLE_NAME,TABLE_SCHEMA
;
+2
Deepak M4y ago
Sachin: getting error 'string_agg' is not a recognized built-in function name
+1
Sachin Singh4y ago
use this
SELECT TABLE_SCHEMA ,
   TABLE_NAME , string_agg(
   (COLUMN_NAME),',') as Cols
FROM   INFORMATION_SCHEMA.COLUMNS
Group By Table_Schema,Table_Name
;
+4