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 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 All Tables and Row Counts
SELECT t.name AS TableName, SUM(p.rows) AS RowCount FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id WHERE p.index_id IN (0,1) GROUP BY t.name ORDER BY RowCount DESC;
2. List All Columns in a Table
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';
3. Find All Stored Procedures
SELECT name, create_date, modify_date FROM sys.procedures ORDER BY modify_date DESC;
4. Find Tables by Column Name
SELECT t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE '%CustomerID%';
5. Get Table Index Information
SELECT
t.name AS TableName,
ind.name AS IndexName,
col.name AS ColumnName
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
ORDER BY
t.name, ind.name;
6. List All Constraints in a Database
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ORDER BY TABLE_NAME;
7. 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.
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_name8. List of views with definition and comments
This query returns the list of database views with their definition SQL and a comment.
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_name9. Table column details
This query returns list of tables and their columns with details.
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;10. Foreign keys
This query returns the list of tables and their foreign keys.
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;11. View columns
This query returns the list of views with their columns.
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;12. Tables by number of columns
This query returns the list of tables sorted by the number of columns they contain.
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;