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

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 😉

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: