Jump To Right Section
Show
In this post, we want to share 6 Useful SQL Server Data Dictionary Queries Every DBA Should Have. Before we get started, if you face problem to activate Microsoft Visual Studio for Product Key, please go through the following article: Visual Studio Product Key Collection.
What is a Data Dictionary?
In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, data types, and views. The data dictionary queries are used by SQL Server to execute and are automatically updated whenever objects are added, removed, or changed within the database.
How SQL Server uses the Data Dictionary
SQL Server uses the database dictionary to verify SQL statements. When you execute a SQL statement the DBMS (Database Management System) parses the statement and then determines whether the tables and fields you are referencing are valid. To do this quickly it references the data dictionary. In addition to testing the validity of statements, SQL Server uses the data dictionary to assist with query plan generation and to reference information defining the structure of the database.
Top 6 Useful SQL Server Data Dictionary Queries
Let’s see This is a list of SQL queries for SQL Server Catalog Views / Data Dictionary handy for any SQL Server DBA.
1. List of tables with a number of rows and comments
This query returns the list of tables in a database sorted by schema and table name with comments and the number of rows in each table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 |
2. List of views with definition and comments
This query returns the list of database views with their definition SQL and a comment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
3. Table columns details
This query returns list of tables and their columns with details.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
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; |
4. Foreign keys
This query returns the list of tables and their foreign keys.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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; |
5. Views columns
This query returns the list of views with their columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
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; |
6. Tables by number of columns
This query returns the list of tables sorted by the number of columns they contain.
|
1 2 3 4 5 6 7 8 9 10 11 |
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; |
The article was published on August 12, 2017 @ 12:15 PM
Leave a Comment