Archive

Archive for February, 2013

T-SQL : How to get columns names of all tables

How to get columns names from all tables of a database ? The query should return all the columns of all tables in a particular database, in a order like how it is stored in the tables

The following queries will perform the same,

Query 1:


SELECT b.name AS TableName,
       a.name AS ColumnName
FROM   sys.columns a
       JOIN sys.tables b
         ON a.object_id = b.object_id
ORDER  BY tablename,
          column_id

Query 2:


SELECT a.table_name,
       column_name
FROM   information_schema.columns a
       JOIN information_schema.tables b
         ON a.table_name = b.table_name
WHERE  b.table_type = 'BASE TABLE'
ORDER  BY a.table_name,
          ordinal_position

Query 3:


SELECT b.name,
       a.name
FROM   sys.all_columns a
       JOIN sys.objects b
         ON a.object_id = b.object_id
WHERE  b.type = 'U'
ORDER  BY b.name,
          a.column_id

Output:
ColumnNames

Happy Querying 😉

%d bloggers like this: