The query below lists all the table columns in a database.

Query

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name,
    col.column_id,
    col.name as column_name,
    t.name as data_type,
    col.max_length,
    col.precision
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by schema_name,
    table_name,
    column_name;

Columns

• schema_name - name of the schema
• table_name - name of the table
• column_id - table column id, starting at 1 for each table
• column_name - name of the column
• data_type - column data type
• max_length - data type max length
• precision - data type precision

Rows

• One row: represents one table column
• Scope of rows: all columns in all tables in a database
• Ordered by: schema, table and column name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free