Code to list xtypes

Code to list xtypes

When comparing two databses we need to know how many tables, stored procedures there is. Now we are migrating sql2000 databses to sql 2005 adn then this code can come in handy. It lists the number if user defined objexts ( excludes all microsoft ones) and also explains what sort of object it is.

create table #tempxtypes (xtype char(2) not null, Meaning varchar(50) not null)
insert into #tempxtypes values(‘C’, ‘CHECK constraint’)
insert into #tempxtypes values(‘D’,’Default or DEFAULT constraint ‘)
insert into #tempxtypes values(‘F’, ‘FOREIGN KEY constraint ‘)
insert into #tempxtypes values(‘L’, ‘Log’)
insert into #tempxtypes values(‘FN’,’Scalar function ‘)
insert into #tempxtypes values(‘IF’, ‘In-lined table-function ‘)
insert into #tempxtypes values(‘P’, ‘Stored procedure ‘)
insert into #tempxtypes values(‘PK’ ,’PRIMARY KEY constraint (type is K) ‘)
insert into #tempxtypes values(‘RF’ ,’Replication filter stored procedure’)
insert into #tempxtypes values(‘S’ ,’System table ‘)
insert into #tempxtypes values(‘TF’,’Table function ‘)
insert into #tempxtypes values(‘TR’ ,’Trigger ‘)
insert into #tempxtypes values(‘U’,’User table’)
insert into #tempxtypes values(‘UQ’,’UNIQUE constraint (type is K)’)
insert into #tempxtypes values(‘V’,’View’)
insert into #tempxtypes values(‘X’,’Extended stored procedure’)

select count(*) ObjectCount ,sysobjects.xtype ,#tempxtypes.Meaning
from sysobjects,#tempxtypes
where objectproperty(object_id(name), ‘IsMSShipped’) = 0
and #tempxtypes.xtype = sysobjects.xtype
group by sysobjects.xtype,#tempxtypes.Meaning
order by sysobjects.xtype desc
drop table #tempxtypes

Från Khan sql dba – mcitp www.addarr.com

Advertisements
%d bloggers like this: