top of page

SQL Server DBA Script for Monitoring Database Object


In this demo i am sharing you some handy script to monitor your database object.


Monitor the list of tables with number of rows and comments


This query will return list of tables in a database according to schema and table name with comments and 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_name;


Monitor the list of views with definition and comments

This query will show list of views with definition and comments.


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;


Table columns details

This query will show list of tables and columns in table 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;



Foreign Keys

This query will show list of tables and 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;


Views and Columns

This query will show list of views with 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;


Tables with number of columns

This query will show 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;


For More Details watch the Video.



Don't forget to subscribe, like and share if it help you.

150 views0 comments

Recent Posts

See All

Many times, I got the question from my colleague or training participants, how to login MySQL if I forgot the password. I have explained the process in many times to them but thought to write this sce

Oracle Database 21c is the first Oracle database release with CDB-only architecture. Oracle 20c was announced only CDB architecture but it was not release for on premises. Only for Cloud. That's why i

bottom of page