How To Create Data Dictionary In Sql Server 2016

This is a list of handy SQL queries to the SQL Server data dictionary. You can also find 100+ other useful queries here.

1. List of tables with number of rows and comments

This query returns list of tables in a database sorted by schema and table name with comments and number of rows in each table.

Query

            select schema_name(tab.schema_id) as schema_name,        tab.name as table_name,         tab.create_date as created,          tab.modify_date as last_modified,         p.rows as num_rows,         ep.value as comments    from sys.tables tab        inner join (select distinct                            p.object_id,                           sum(p.rows) rows                      from sys.tables t                           inner join sys.partitions p                                on p.object_id = t.object_id                      group by p.object_id,                           p.index_id) p             on p.object_id = tab.object_id         left join sys.extended_properties ep              on tab.object_id = ep.major_id            and ep.name = 'MS_Description'            and ep.minor_id = 0            and ep.class_desc = 'OBJECT_OR_COLUMN'   order by schema_name,         table_name

Rows

One row represents one table. All tables will be included.

Columns

Column	Meaning
SCHEMA_NAME	Schema name.
TABLE_NAME	Table name.
CREATED	Table creation date and time.
LAST_MODIFIED	Table last modification date and time.
NUM_ROWS	Number of rows in the table.
COMMENTS	Table comments.

Sample results

2. List of views with definition and comments

This query returns list of database views with their definition SQL and a comment.

Query

            select schema_name(v.schema_id) as schema_name,        v.name as view_name,        v.create_date as created,        v.modify_date as last_modified,        m.definition,        ep.value as comments   from sys.views v        left join sys.extended_properties ep             on v.object_id = ep.major_id           and ep.name = 'MS_Description'           and ep.minor_id = 0           and ep.class_desc = 'OBJECT_OR_COLUMN'        inner join sys.sql_modules m             on m.object_id = v.object_id  order by schema_name,           view_name

Rows

One row represents one view.

Columns

Column	Meaning
SCHEMA_NAME	Schema name.
VIEW_NAME	View name.
CREATED	View creation date and time.
LAST_MODIFIED	View last modification date and time.
DEFINITION	View definition (SQL query).
COMMENTS	View comments.

Sample results

3. Table columns details

This query returns list of tables and their columns with details.

Query

            select schema_name(tab.schema_id) as schema_name,        tab.name as table_name,         col.name as column_name,         t.name as data_type,            t.name +         case when t.is_user_defined = 0 then                   isnull('(' +                   case when t.name in ('binary', 'char', 'nchar',                             'varchar', 'nvarchar', 'varbinary') then                            case col.max_length                                  when -1 then 'MAX'                                  else                                       case when t.name in ('nchar',                                                 'nvarchar') then                                                cast(col.max_length/2                                                 as varchar(4))                                            else cast(col.max_length                                                 as varchar(4))                                       end                            end                       when t.name in ('datetime2', 'datetimeoffset',                             'time') then                             cast(col.scale as varchar(4))                       when t.name in ('decimal', 'numeric') then                             cast(col.precision as varchar(4)) + ', ' +                             cast(col.scale as varchar(4))                  end + ')', '')                     else ':' +                   (select c_t.name +                           isnull('(' +                           case when c_t.name in ('binary', 'char',                                     'nchar', 'varchar', 'nvarchar',                                     'varbinary') then                                      case c.max_length                                           when -1 then 'MAX'                                           else                                                  case when t.name in                                                          ('nchar',                                                          'nvarchar') then                                                          cast(c.max_length/2                                                         as varchar(4))                                                    else cast(c.max_length                                                         as varchar(4))                                               end                                     end                               when c_t.name in ('datetime2',                                     'datetimeoffset', 'time') then                                     cast(c.scale as varchar(4))                               when c_t.name in ('decimal', 'numeric') then                                    cast(c.precision as varchar(4)) + ', '                                     + cast(c.scale as varchar(4))                          end + ')', '')                      from sys.columns as c                          inner join sys.types as c_t                               on c.system_type_id = c_t.user_type_id                    where c.object_id = col.object_id                      and c.column_id = col.column_id                      and c.user_type_id = col.user_type_id                  )         end as data_type_ext,         case when col.is_nullable = 0 then 'N'               else 'Y' end as nullable,         case when def.definition is not null then def.definition               else '' end as default_value,         case when pk.column_id is not null then 'PK'               else '' end as primary_key,          case when fk.parent_column_id is not null then 'FK'               else '' end as foreign_key,          case when uk.column_id is not null then 'UK'               else '' end as unique_key,         case when ch.check_const is not null then ch.check_const               else '' end as check_contraint,         cc.definition as computed_column_definition,         ep.value as comments    from sys.tables as tab         left 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         left join sys.default_constraints as def             on def.object_id = col.default_object_id         left join (                   select index_columns.object_id,                           index_columns.column_id                     from sys.index_columns                          inner join sys.indexes                               on index_columns.object_id = indexes.object_id                             and index_columns.index_id = indexes.index_id                    where indexes.is_primary_key = 1                   ) as pk              on col.object_id = pk.object_id             and col.column_id = pk.column_id         left join (                   select fc.parent_column_id,                           fc.parent_object_id                     from sys.foreign_keys as f                           inner join sys.foreign_key_columns as fc                               on f.object_id = fc.constraint_object_id                    group by fc.parent_column_id, fc.parent_object_id                   ) as fk             on fk.parent_object_id = col.object_id             and fk.parent_column_id = col.column_id             left join (                   select c.parent_column_id,                           c.parent_object_id,                           'Check' check_const                     from sys.check_constraints as c                    group by c.parent_column_id,                          c.parent_object_id                   ) as ch             on col.column_id = ch.parent_column_id            and col.object_id = ch.parent_object_id         left join (                   select index_columns.object_id,                           index_columns.column_id                     from sys.index_columns                          inner join sys.indexes                               on indexes.index_id = index_columns.index_id                             and indexes.object_id = index_columns.object_id                     where indexes.is_unique_constraint = 1                     group by index_columns.object_id,                            index_columns.column_id                   ) as uk             on col.column_id = uk.column_id             and col.object_id = uk.object_id         left join sys.extended_properties as ep              on tab.object_id = ep.major_id            and col.column_id = ep.minor_id            and ep.name = 'MS_Description'            and ep.class_desc = 'OBJECT_OR_COLUMN'         left join sys.computed_columns as cc             on tab.object_id = cc.object_id            and col.column_id = cc.column_id   order by schema_name,         table_name,          column_name;

Rows

One row represents one table column.

Columns

Column	Meaning
SCHEMA_NAME	Schema name.
TABLE_NAME	Table name.
COLUMN_NAME	Column name.
DATA_TYPE	Data type. For instance, varchar or decimal.
DATA_TYPE_EXT	Data type with information about scale/precision or string length. For instance, varchar(100) or decimal(8, 2).
NULLABLE	Nullable flag. "Y" if column is nullable, "N" if column is not nullable.
DEFAULT_VALUE	Column default value.
PRIMARY_KEY	Primary key flag. "PK" when column is part of table primary key.
FOREIGN_KEY	Foreign key flag. "FK" when column is part of foreign key.
UNIQUE_KEY	Unique key flag. "UK" when column is part of unique key.
CHECK_CONSTRAINT	Check constraint flag. "Check" when column is part of check constraint.
COMPUTED_COLUMN_DEFINITION	Computed column definition (not null only if column is computed).
COMMENTS	Column comments.

Sample results

4. Foreign keys

This query returns list of tables and their foreign keys.

Query

            select schema_name(tab.schema_id) as table_schema_name,        tab.name as table_name,        col.name as column_name,        fk.name as constraint_name,        schema_name(tab_prim.schema_id) as primary_table_schema_name,        tab_prim.name as primary_table_name,        col_prim.name as primary_table_column,         schema_name(tab.schema_id) + '.' + tab.name + '.' +              col.name + ' = ' + schema_name(tab_prim.schema_id) + '.' +              tab_prim.name + '.' + col_prim.name as join_condition,        case             when count(*) over (partition by fk.name) > 1 then 'Y'             else 'N'        end as complex_fk,        fkc.constraint_column_id as fk_part   from sys.tables as tab        inner join sys.foreign_keys as fk            on tab.object_id = fk.parent_object_id        inner join sys.foreign_key_columns as fkc            on fk.object_id = fkc.constraint_object_id        inner join sys.columns as col            on fkc.parent_object_id = col.object_id           and fkc.parent_column_id = col.column_id        inner join sys.columns as col_prim            on fkc.referenced_object_id = col_prim.object_id           and fkc.referenced_column_id = col_prim.column_id        inner join sys.tables as tab_prim            on fk.referenced_object_id = tab_prim.object_id  order by table_schema_name,        table_name,         primary_table_name,         fk_part;

Rows

One row represents one pair of columns in foreign key.

Columns

Column	Meaning
TABLE_SCHEMA_NAME	Foreign table schema name.
TABLE_NAME	Foreign table name.
COLUMN_NAME	Foreign table column name.
CONSTRAINT_NAME	Constraint name.
PRIMARY_TABLE_SCHEMA_NAME	Primary table schema name.
PRIMARY_TABLE_NAME	Primary table name.
PRIMARY_TABLE_COLUMN	Primary table column name.
JOIN_CONDITION	Join condition containing foreign and primary key tables and columns.
COMPLEX_FK	Complex foreign key flag. "Y" when foreign key is complex, otherwise "N".
FK_PART	Represents part number of foreign key.

Sample results

5. Views columns

This query returns list of views with their columns.

Query

            select schema_name(v.schema_id) as schema_name,        v.name as view_name,         col.name as column_name,        t.name as data_type,        t.name +         case when t.is_user_defined = 0 then                   isnull('(' +                   case when t.name in ('binary', 'char', 'nchar',                            'varchar', 'nvarchar', 'varbinary') then                            case col.max_length                                  when -1 then 'MAX'                                  else                                       case                                           when t.name in ('nchar',                                                'nvarchar') then                                               cast(col.max_length/2                                                as varchar(4))                                          else cast(col.max_length                                                as varchar(4))                                      end                            end                       when t.name in ('datetime2',                             'datetimeoffset', 'time') then                              cast(col.scale as varchar(4))                       when t.name in ('decimal', 'numeric') then                             cast(col.precision as varchar(4)) + ', ' +                            cast(col.scale as varchar(4))                  end + ')', '')                     else ':' +                  (select c_t.name +                           isnull('(' +                           case when c_t.name in ('binary', 'char',                                    'nchar', 'varchar', 'nvarchar',                                    'varbinary') then                                    case c.max_length                                         when -1 then 'MAX'                                         else case when t.name in                                                        ('nchar',                                                         'nvarchar')                                                   then cast(c.max_length/2                                                        as varchar(4))                                                   else cast(c.max_length                                                        as varchar(4))                                              end                                    end                               when c_t.name in ('datetime2',                                     'datetimeoffset', 'time') then                                    cast(c.scale as varchar(4))                               when c_t.name in ('decimal', 'numeric') then                                    cast(c.precision as varchar(4)) +                                    ', ' + cast(c.scale as varchar(4))                          end + ')', '')                     from sys.columns as c                          inner join sys.types as c_t                               on c.system_type_id = c_t.user_type_id                    where c.object_id = col.object_id                      and c.column_id = col.column_id                      and c.user_type_id = col.user_type_id                  )         end as data_type_ext,        case when col.is_nullable = 0 then 'N' else 'Y' end as nullable,        ep.value as comments   from sys.views as v        join sys.columns as col            on v.object_id = col.object_id        left join sys.types as t            on col.user_type_id = t.user_type_id        left join sys.extended_properties as ep             on v.object_id = ep.major_id           and col.column_id = ep.minor_id           and ep.name = 'MS_Description'                   and ep.class_desc = 'OBJECT_OR_COLUMN'  order by schema_name,        view_name,        column_name;

Rows

One row represents one view column.

Columns

Column	Meaning
SCHEMA_NAME	Schema name.
VIEW_NAME	View name.
COLUMN_NAME	View column name.
DATA_TYPE	Data type. For instance, varchar or decimal.
DATA_TYPE_EXT	Data type with information about scale/precision or string length. For instance, varchar(100) or decimal(8, 2).
NULLABLE	Nullable flag. "Y" if column is nullable, "N" if column is not nullable.
COMMENTS	Column comments.

Sample results

6. Tables by number of columns

This query returns list of tables sorted by the number of columns they contain.

Query

            select schema_name(tab.schema_id) as schema_name,         tab.name as table_name,         count(*) as columns   from sys.tables as tab        inner join sys.columns as col            on tab.object_id = col.object_id   group by schema_name(tab.schema_id),         tab.name  order by count(*) desc;

Rows

One row represents one user table.

Columns

Column	Meaning
SCHEMA_NAME	Schema name.
TABLE_NAME	Table name.
COLUMNS	Number of columns table contains.

Sample results