May 10, 2012

sp_MSdependencies (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdependencies(nvarchar @objname
, int @objtype
, int @flags
, nvarchar @objlist
, int @intrans)

MetaData:

   
create procedure sys.sp_MSdependencies
@objname nvarchar(517) = null, @objtype int = null, @flags int = 0x01fd, @objlist nvarchar(128) = null, @intrans int = null
as
set deadlock_priority low

create table #t1 (
tid int NULL,
ttype smallint NULL,
tcat smallint NULL,
pid int NULL,
ptype smallint NULL,
pcat smallint NULL,
bDone smallint NULL
)
create table #t2 (
tid int NULL,
ttype smallint NULL,
tcat smallint NULL,
pid int NULL,
ptype smallint NULL,
pcat smallint NULL,
bDone smallint NULL
)

create table #t21(
pid int NULL,
ptype smallint NULL,
pcat smallint NULL,
depid int NULL
)


create table #tempudt (
dtype int NOT NULL
)

-- Worktables we'll use for optimization. --
create table #t3 (
tid int NOT NULL
)
create table #t4 (
tid int NOT NULL
)
-- create clustered index #ci_t3 on #t3(tid) with allow_dup_row --
-- create clustered index #ci_t4 on #t4(tid) with allow_dup_row --
create clustered index #ci_t3 on #t3(tid)
create clustered index #ci_t4 on #t4(tid)
create table #temptrig(
id int NOT NULL,
deltrig int NOT NULL,
sysstat smallint NOT NULL,
category int NOT NULL
)
-- create clustered index #ci_temptrig on #temptrig (deltrig) with allow_dup_row --
create clustered index #ci_temptrig on #temptrig (deltrig)

-- 8.0 The new UDF is taking 0x0001, and we have to re-assign UDDT --
if (@objname = N'?')
begin
print N'sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd'
print N' name: name or null (all objects of type)'
print N' type: type number (see below) or null'
print N' if both null, get all objects in database'
print N' flags is a bitmask of the following values:'
print N' 0x10000 = return multiple parent/child rows per object'
print N' 0x20000 = descending return order'
print N' 0x40000 = return children instead of parents'
print N' 0x80000 = Include input object in output result set'
print N' 0x100000 = return only firstlevel (immediate) parents/children'
print N' 0x200000 = return only DRI dependencies'
print N' power(2, object type number(s)) to return in results set:'
print N' 0 (1 - 0x0001) - UDF'
print N' 1 (2 - 0x0002) - system tables or MS-internal objects'
print N' 2 (4 - 0x0004) - view'
print N' 3 (8 - 0x0008) - user table'
print N' 4 (16 - 0x0010) - procedure'
print N' 5 (32 - 0x0020) - log'
print N' 6 (64 - 0x0040) - default'
print N' 7 (128 - 0x0080) - rule'
print N' 8 (256 - 0x0100) - trigger'
print N' 12 (1024 - 0x0400) - uddt'
print N' shortcuts:'
print N' 29 (0x011c) - trig, view, user table, procedure'
print N' 448 (0x00c1) - rule, default, datatype'
print N' 4606 (0x11fd) - all but systables/objects'
print N' 4607 (0x11ff) - all'
return 0
end

-- If this proc is called in a tight loop, it tends to fill up the log in a small tempdb too fast --
-- for the trunc. log on chkpt thread to keep up. So help it out here. --
-- I can do this only if the current login has the proper permission to dump tempdb --
-- In order to find out this information, I need to switch to tempdb --
declare @origdb nvarchar(128)
declare @tempdbName nvarchar(258)
select @origdb = db_name()
SELECT @tempdbName = REPLACE(@origdb, N']', N']]')

-- If they want SQLDMODep_DRIOnly, remove all but usertable objects from @flags --
if (@flags & 0x200000 <> 0)
select @flags = (@flags & ~convert(int, 0x05ff)) | power(2, 3)

if (@objtype in (12, 5, 6, 7))
begin
-- Print only, do not raiserror as we may be calling this blindly and this is not a real error. --
print N'Rules, defaults, and datatypes do not have dependencies.'
return (0)
end

--
* Create #t1 and #t2 as temp object holding areas. Columns are:
* tid - temp object id
* ttype - temp object type
* pid - parent or child object id
* ptype - parent or child object type
* bDone - NULL means dependencies not yet evaluated, else nonNULL.
--
declare @curid int, @curcat int, @rowsaffected int
declare @allobjs int
declare @delinputobj int
select @allobjs = 0, @delinputobj = 0, @curid = NULL, @curcat = NULL

--
* If both name and type are null, this means get every object in the
* database matching the specification they passed in. Otherwise,
* find the passed object or all objects of the passed type. Start off
* loading parent info (pid, tid); these will be put into child as needed.
* If Objlist is specified we simply load its contents into #t1.
--
if (@objlist is not null)
begin
declare @cmd nvarchar(1024)
set @cmd = N'insert #t1 (pid, ptype, pcat) select l.objid, l.objtype, o.category &2'+
N' from ' + QUOTENAME(@objlist, ']') + N' l, dbo.sysobjects o where o.id = l.objid '

exec(@cmd)

end else begin
if (@objname is null and @objtype is null)
begin
set nocount on
select @allobjs = 1
insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from dbo.sysobjects o
where ((power(2, o.sysstat & 0x0f) & 0x05ff) <> 0) and (OBJECTPROPERTY(o.id, N'IsDefaultCnst') <> 1 and OBJECTPROPERTY(o.id, N'IsRule') <> 1 )
end else begin
if (@objname is not null)
begin
select @curid = id, @objtype = o.sysstat & 0x0f, @curcat = o.category & 0x0002 from dbo.sysobjects o where id = object_id(@objname)
if (@curid is null)
begin
RAISERROR (15001, -1, -1, @objname)
return 1
end
if (@flags & 0x80000 = 0)
select @delinputobj = @curid
end

set nocount on
if (@curid is null)
insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from dbo.sysobjects o
where o.sysstat & 0x0f = @objtype
else
insert #t1 (pid, ptype, pcat) values (@curid, @objtype, @curcat)
end
end
--
* All initial objects are loaded as parents/children. Now we loop, creating
* rows of child/parent relationships. Use #t2 as a temp area for the selects
* to simulate recursion; when they find no rows, we're done with this step.
*
* Note that triggers are weird; they'
re part of a table definition but can
* also reference other tables, so we need to evaluate them both ways. SQL
* Server stores the table for a trigger object as its deltrig; if a trigger
* references another table, that relationship is stored in sysdepends.
* This peculiarity of triggers requires separating the object-retrieval pass
* from the creation-sequence pass (below). Also, the fact that trigger tables
* are stored in a non-indexed column (deltrig) requires us to use a worktable
* if we're returning triggers, so we don't continually tablescan sysobjects.
--

if (@flags & power(2, 8) != 0)
insert #temptrig select d.id, d.deltrig, d.sysstat, d.category from dbo.sysobjects d where OBJECTPROPERTY(d.id, N'IsTrigger') = 1

while (select count(*) from #t1 where bDone is null) > 0
begin
--
* Remove Microsoft-internal or other system objects from #t1, unless
* @flags specified including system tables. We do this here so that
* cascaded system dependencies are not included unless specifically
* requested. For other restrictions, we wait until below so that all
* cascaded object types are fully evaluated.
--
if (@flags & power(2, 1) = 0)
delete #t1 where ttype = 1 or tcat = 0x0002 or pcat = 0x0002

if (@flags & 0x40000 != 0)
begin
if (@flags & 0x200000 = 0) begin
-- Table -- > Triggers --
if (@flags & power(2, 8) != 0)
insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, o.id, o.sysstat & 0x0f, o.category & 0x0002 from #t1 t, #temptrig o
where t.bDone is null and t.ptype = 3 and o.deltrig = t.pid

-- Object -- > sysdepends children --
insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, d.id, o.sysstat & 0x0f, o.category & 0x0002
from #t1 t, dbo.sysdepends d, dbo.sysobjects o
where t.bDone is null and d.depid = t.pid and d.id = o.id
end

-- Object -- > sysreferences children (FK tables referencing this one) --
insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, r.fkeyid, o.sysstat & 0x0f, o.category & 0x0002
from #t1 t, dbo.sysreferences r, dbo.sysobjects o
where t.bDone is null and r.rkeyid = t.pid and r.fkeyid = o.id
end else begin
if (@flags & 0x200000 = 0) begin
-- Trigger -- > Table --
if (@flags & power(2, 3) != 0)
insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, o.deltrig, u.sysstat & 0x0f, u.category & 0x0002
from #t1 t, dbo.sysobjects o, dbo.sysobjects u
where t.bDone is null and t.ptype = 8 and o.id = t.pid and o.deltrig != 0 and u.id = o.deltrig

-- Object -- > sysdepends parents --
insert #t21 (pid, ptype, pcat, depid)
select distinct t.pid, t.ptype, t.pcat, d.depid
from #t1 t, dbo.sysdepends d
where t.bDone is null and d.id = t.pid

insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, t.depid, o.sysstat & 0x0f, o.category & 0x0002
from #t21 t, dbo.sysobjects o
where t.depid = o.id
end
-- Object -- > sysreferences parents (PK/UQ tables referenced by one) --
insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
select distinct t.pid, t.ptype, t.pcat, r.rkeyid, o.sysstat & 0x0f, o.category & 0x0002
from #t1 t, dbo.sysreferences r, dbo.sysobjects o
where t.bDone is null and r.fkeyid = t.pid and r.rkeyid = o.id
end

--
* We have this generation of parents in #t2, so clear the current
* child generation's bDone flags. Then insert from #t2; the current
* parent generation becomes the next loop'
s child generation, with
* bDone = null until next loop's dependencies are selected.
--
update #t1 set bDone = 1
insert #t1 select * from #t2 where #t2.tid not in
(select tid from #t1 where #t1.tid = #t2.tid and #t1.pid = #t2.pid)
truncate table #t2

-- If they only want one level, we'
re done. --
if (@flags & 0x100000 <> 0)
update #t1 set bDone = 1
end

--
* The inner loop above did not put parents with no parents into the
* child (tid) list. Do that now, then remove all rows where tid is
* NULL, because these were initial objects which now have a tid row.
* Just in case, remove self-refs from #t1, and also remove rows from #t1
* with NULL pid if a row exists for that tid where the pid is nonNULL.
* Avoid nested self-joins by using worktables.
--
truncate table #t3
insert #t3 select tid from #t1 where tid is not null
and tid <> pid -- make sure self-refs with no other refs go in child list
-- update statistics #t3 #ci_t3 --
insert #t1 (tid, ttype, tcat, bDone) select distinct pid, ptype, pcat, 0 from #t1 t
where t.pid is not null and not exists (select * from #t3 where tid = t.pid)
delete #t1 where tid = pid -- now remove self-refs

--
* Because triggers can go in both directions, we'll need to check for
* circular dependencies on parent evaluation. Since any tables referenced
* by the trigger must exist before the trigger can be created, remove rows
* where the trigger is the parent.
--
if (@flags & 0x40000 = 0)
delete #t1 where ptype = 8

truncate table #t3
insert #t3 select tid from #t1 where tid is not null and pid is not null
-- update statistics #t3 #ci_t3 --
delete #t1 where #t1.tid is null or #t1.tid = #t1.pid
or (#t1.pid is null and exists (select * from #t3 where tid = #t1.tid))

--
* If we'
re to get all objects, get all UDDTs (which aren't in dbo.sysobjects)
* and Rules/Defaults, assuming we'
re returning those types.
--
if (@allobjs <> 0)
begin
if (@flags & power(2, 12) != 0)
insert #tempudt
select xusertype from dbo.systypes where xusertype > 256
if (@flags & (power(2, 7) | power(2, 6)) != 0)
insert #t2 (tid, ttype, tcat)
select id, sysstat & 0x0f, 0 from dbo.sysobjects
where (OBJECTPROPERTY(id, N'IsRule') = 1 or OBJECTPROPERTY(id, N'IsDefault') = 1)
and category & 0x0800 = 0
end else begin
--
* Not getting all objects. Get any datatypes that
* are referenced by objects in #t1. We don't care about specific
* datatype dependencies, we just want to know which ones are needed.
--
if (@flags & power(2, 12) != 0)
insert #tempudt select distinct xusertype from dbo.syscolumns
where xusertype > 256 and id in (select tid from #t1)

--
* Load rules and defaults needed by datatypes and other #t1 objects
* into #t2. Don'
t track specific object dependencies with these;
* we just want to know which ones are needed. For defaults only, eliminate
* those which are constraints.
--
if (@flags & power(2, 7) != 0)
begin
insert #t2 (tid, ttype, tcat)
select distinct s.domain, 7, 0 from dbo.systypes s, #tempudt t
where s.domain != 0 and s.xusertype = t.dtype
and s.domain not in (select tid from #t1)
insert #t2 (tid, ttype, tcat)
select distinct s.domain, 7, 0 from dbo.syscolumns s, #t1 t
where s.domain != 0 and s.id = t.tid
and s.domain not in (select tid from #t1)
end
if (@flags & power(2, 6) != 0)
begin
insert #t2 (tid, ttype, tcat)
select distinct s.tdefault, 6, 0 from dbo.systypes s, #tempudt t
where s.tdefault != 0 and s.xusertype = t.dtype
and s.tdefault not in (select tid from #t1)
and s.tdefault not in (select id from dbo.sysobjects where category & 0x0800 != 0)
insert #t2 (tid, ttype, tcat)
select distinct s.cdefault, 6, 0 from dbo.syscolumns s, #t1 t
where s.cdefault != 0 and s.id = t.tid
and s.cdefault not in (select tid from #t1)
and s.cdefault not in (select id from dbo.sysobjects where category & 0x0800 != 0)
end
end -- Not getting all objects --

--
* Now that we've got all objects we want, eliminate those we don't
* want to return. If @inputobj and they don't want it returned,
* remove it from the table. Then eliminate object types they don'
t
* want returned. Make sure that in doing so we retain all parent
* objects of the types we do want -- it is possible at this point
* that a tid we want has no rows except those with pids we don't want.
--
if (@flags & 0x05ff != 0x05ff or @delinputobj != 0)
begin
delete #t1 where @flags & power(2, ttype) = 0 or tid = @delinputobj

--
* Be sure that the insert does not duplicate rows that will survive the
* following delete -- these are rows where the pid is not @delinputobj
* and ptype is either null or a type we'
ll keep (if ptype is null then
* pid hasn't been set so no need for more complex checking).
--
insert #t1 (tid, ttype, tcat) select distinct tid, ttype, tcat from #t1
where (@flags & power(2, ptype) = 0 or pid = @delinputobj)
and tid not in (select tid from #t1 where ptype is null or
(pid != @delinputobj and @flags & power(2, ptype) != 0))
delete #t1 where @flags & power(2, ptype) = 0 or pid = @delinputobj
end

--
* To determine creation order, find all objects which are not yet bDone
* and have no parents or whose parents are all bDone, and set their bDone
* to the next @curid. This will leave bDone as the ascending order in
* which objects must be created (topological sort). Again, use worktables
* to remove nested self-joins.
--
update #t1 set bDone = 0
select @curid = 1, @rowsaffected = 1
while (@rowsaffected <> 0)
begin
if (@flags & 0x40000 != 0) begin
truncate table #t3
insert #t3 select pid from #t1 where pid is not null and bDone = 0
-- update statistics #t3 #ci_t3 --
update #t1 set bDone = @curid where bDone = 0 and tid not in (select tid from #t3)
end else begin
truncate table #t3
truncate table #t4
insert #t3 select tid from #t1 where bDone = 0 -- Parents not yet done --
-- update statistics #t3 #ci_t3 --
insert #t4 select tid from #t1 -- TIDs with (parents not yet done) --
where pid is not null and pid in (select tid from #t3)
-- update statistics #t4 #ci_t4 --
update #t1 set #t1.bDone = @curid where #t1.bDone = 0 -- TIDs who are not (TIDs with (parents not yet done)) --
and not exists (select * from #t4 where tid = #t1.tid)
end
select @rowsaffected = @@rowcount, @curid = @curid + 1
end

-- For SQL60 only, we need to check circular dependencies (DRI for tables is the only way to get them). --
-- This will have occurred if we still have any rows in #t1 where bDone = 0, after the above loop. --
--
* At this point, these are indirect (a->b->...->a), and can only be created by:
* create table a; create table b ref a; alter table a ref b
* There is thus no way to create the tables in a single pass. Further, the ALTER
* TABLE B must be done AFTER data has been added (else the PK/FK will fail).
* Therefore, the two-step model of
* - Create tables (and other objects)
* - Transfer data
* does not work, so assume anyone doing this will do it in three passes (e.g. ScriptTransfer):
* - Create tables (and other objects) but no references (also defer some indexing, for perfomance)
* - Transfer data
* - Create references (and deferred indexing)
* and just set bDone for everything remaining to @curid.
--
if exists (select * from #t1 where bDone = 0) begin
-- select "Circular Dependencies", object_name(tid) from #t1 where bDone = 0
-- RAISERROR (14300, -1, -1)
-- return 1
update #t1 set bDone = @curid where bDone = 0
end

--
* Finally, return the objects. Rules/Defaults must be created first so they'
re returned first,
* followed by UDDTs. followed by all other (sysdepends/DRI) dependencies. @curid is the bDone
* value; we need to increment the #t1 value so our multi-result-set is in the proper sequence.
* Of course, these never have parents, so don't return them if asking for children.
--
if (@flags & 0x40000 = 0) begin
select @curid = 1
if ((@flags & (power(2, 7) | power(2, 6)) != 0) and exists (select * from #t2)) begin
update #t1 set bDone = bDone + 1
select distinct oType = power(2, o.sysstat & 0x0f), oRuleDefName = o.name, oOwner = schema_name(syso.schema_id), oSequence = convert(smallint, @curid)
from dbo.sysobjects o, #t2 t, sys.all_objects syso
where o.id = t.tid and o.id = syso.object_id
order by power(2, o.sysstat & 0x0f), o.name
select @curid = @curid + 1
end
if ((@flags & power(2, 12) != 0) and exists (select * from #tempudt)) begin
update #t1 set bDone = bDone + 1
select distinct oType = power(2, 12), oUDDTName = c.name, oOwner = schema_name(syst.schema_id), oSequence = convert(smallint, @curid)
from dbo.systypes c, #tempudt t, dbo.sysobjects p, sys.types syst
where c.xusertype = t.dtype and c.xusertype = syst.user_type_id
order by c.name
select @curid = @curid + 1
end
end

--
* Select dependency-style objects, returning parents if desired.
* Normally sorting is in terms of who must be created first, i.e. ascending: parent-- >child-- >grandchild.
* Descending order (child-- >parent-- >grandparent) would be used for a graphical-dependencies evaluator showing
* the parents. Therefore we invert bDone if descending sort. bDone is 1-based; min + max - bDone gives inversion.
* Note: Always return at least this empty set.
--
if (@flags & 0x20000 != 0) begin
select @curid = max(bDone) + min(bDone) from #t1
update #t1 set bDone = convert(smallint, @curid) - bDone
end
if (@flags & 0x10000 != 0)
select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = schema_name(sysoo.schema_id),
-- RelType = power(2, p.sysstat & OBJTYPE_BITS), RelName = p.name, RelOwner = user_name(p.uid), --
RelType = case when (p.name is not null) then power(2, p.sysstat & 0x0f) else 0 end, RelName = p.name, RelOwner = schema_name(sysop.schema_id),
oSequence = t.bDone
from dbo.sysobjects o, dbo.sysobjects p right join #t1 t on p.id = t.pid, sys.all_objects sysoo, sys.all_objects sysop
where o.id = t.tid and o.id = sysoo.object_id and p.id = sysop.object_id
order by t.bDone, power(2, o.sysstat & 0x0f), o.name
else
select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = schema_name(syso.schema_id),
oSequence = t.bDone
from dbo.sysobjects o, sys.all_objects syso, #t1 t
where o.id = t.tid and o.id = syso.object_id
order by t.bDone, power(2, o.sysstat & 0x0f), o.name

No comments:

Post a Comment

Total Pageviews