Get column names of all tables in SQL

Question

I need a query to generate column names (and it's type if possible) of all tables in database.

Is there any simple way?

Answer 1

You can use INFORMATION_SCHEMA.COLUMNS:

select c.*
from INFORMATION_SCHEMA.COLUMNS c;

This has name, type, and a lot of other information for all tables in a database -- note, not on a server but in a database.

Answer 2

You can use INFORMATION_SCHEMA.COLUMNS to list the columns of a given table.

create table tbl1 (id int)
Select * from INFORMATION_SCHEMA.COLUMNS where table_name in ('tbl1')

Demo

You can also use sys.columns as shown below

SELECT TAB.name AS TableName
    ,TAB.object_id AS ObjectID
    ,COL.name AS ColumnName
    ,COL.user_type_id AS DataTypeID
FROM sys.columns COL
INNER JOIN sys.tables TAB ON COL.object_id = TAB.object_id
    -- where TAB.name = '<TABLENAME>'
    -- Uncomment above line and add <Table Name> to fetch details for particular table
    -- where COL.name = '<COLUMNNAME>'
    -- Uncomment above line and add <Column Name> to fetch details for particular column names

Demo of sys.columns.

For the complete information of different types of result for a given table i.e., column name, data type and there size you can refer this reference.

Hope it will help you.