List all columns in specific table in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-08

Table of Contents:


    Query below returns all columns from a speficic table in SQL Server database.

    Query

    select 
        col.column_id as id,
        col.name,
        t.name as data_type,
        col.max_length,
        col.precision,
        col.is_nullable
    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
    where tab.name = 'Table name' -- enter table name here
    -- and schema_name(tab.schema_id) = 'Schema name'
    order by tab.name, column_id;
    

    Important:

    • please edit condition and type in your table name
    • you can also uncomment schema condition and provide your table schema name to further filter tables (in case tables in different schemas have same name).

    Columns

    • id - column position in table, starting at 1
    • name - column name
    • data_type - column data type
    • max_length - data type max length
    • precision - data type precision
    • is_nullable - flag if column is nullable:
      • 0 - not nullable (doesn't allow nulls)
      • 1 - nullable (allows nulls)

    Rows

    • One row represents one column in a table
    • Scope of rows: all columns in speficic table
    • Ordered by column id (position in table)

    Sample results

    Columns in Product table in AdventureWorks database.

    You could also get this

    Get this interactive HTML data dictionary in minutes with Dataedo.

    See live HTML data dictionary sample

    Try for free

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept