How to get all columns name in SQL Server.

Question

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...

Amit Mohanty · 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

Sachin Singh · Answer

use this SELECT TABLE_SCHEMA , TABLE_NAME , string_agg( (COLUMN_NAME), ',' ) as Cols FROM INFORMATION_SCHEMA.COLUMNS Group By Table_Schema,Table_Name ;

Sachin Singh · Answer

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 ;

Deepak M · Answer

Sachin: getting error 'string_agg' is not a recognized built-in function name

How to get all columns name in SQL Server.

Answers (4)