6 Useful SQL Server Data Dictionary Queries Every DBA Should Have

Table of Contents:


    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

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