Tuesday 11 November 2008

Microsoft SQL Server 2000 Data Dictionary view to view all objects in the database

See all objects in your Microsoft SQL database and their types.

This is one of my favorite data dictionary queries in MS SQL Server. It shows me quickly what makes up a database, how many tables, triggers, views etc.

I have been using it on MS SQL Server 2000 and it works!


select owner, cnt, object_type =
CASE xtype
when 'C' then 'CHECK constraint'
when 'D' then 'Default or DEFAULT constraint'
when 'F' then 'FOREIGN KEY constraint'
when 'L' then 'Log'
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'PK' then 'PRIMARY KEY constraint (type is K)'
when 'RF' then 'Replication filter stored procedure'
when 'S' then 'System table'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User table'
when 'Q' then 'UNIQUE constraint (type is K)'
when 'V' then 'View'
else 'unknown'
end
from
(
select su.name owner, so.uid uid, so.xtype xtype,count(*) cnt from sysobjects so, sysusers su
where so.uid = su.uid
group by su.name, so.xtype, so.uid
) sysobjects_info



Owner Count Object Type
---------- ------- ----------------------------------
dbo 12 CHECK constraint
dbo 363 Default or DEFAULT constraint
dbo 127 FOREIGN KEY constraint
xanthi 34 Stored procedure
dbo 241 Stored procedure
dbo 234 PRIMARY KEY constraint (type is K)
dbo 19 System table
dbo 10 Trigger
dbo 399 User table
dbo 6 unknown
dbo 11 View

No comments: