Technical Article

Object dependencies in xml format

,

Since we have sys.dm_sql_referenced_entities, dynamic view, which contains the dependency tree, I gave it a try and used it to display all objects in xml format.
The stored procedure uses the view to retrieve the hierarchy, stores it in a table (#tree) which is later traversed using recursive CTE.
Just create the stored procedure, pass it an object name and expand the xml result.
I didn't include check for the level of recursion, but we all know that it'll break if the depth reaches 32. I don't mean to preach, but if any of your sql objects have dependency that deep, you're in trouble.
create proc [dbo].[get_Dependencies_xml] (@object sysname)
as
begin
set nocount on
declare @obj_id int

select @obj_id=object_id(@object)
if not exists (select id from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#tree') and xtype='u')
create table #tree (objectid int,parentid int,objectname sysname,xtype char(2),objecttype varchar(32),processed bit)

insert #tree 
select distinct 
@obj_id 
,null
,@object
,xtype
,case xtype
when 'p' then 'procedure'
when 'fn' then 'function'
when 'if' then 'inline table function'
when 'x' then 'extended proc'
when 'tf' then 'table function'
when 'ft' then 'clr table function'
when 'fs' then 'clr function'
when 'pc' then 'clr proc'
when 'af' then 'clr aggregate function'
when 'u' then 'table'
when 'v' then 'view'
when 'sn' then 'synonym'
end as object_type 
,0
from sysobjects where @obj_id=id

while exists(select * from #tree where isnull(processed,0)=0) 
begin
select top 1 @object=objectname,@obj_id=objectid from #tree where isnull(processed,0)=0
update #tree set processed=1 where objectname =@object and objectid=@obj_id
insert #tree 
select distinct 
referenced_id
,@obj_id parentid
,isnull(referenced_schema_name+'.','')+referenced_entity_name
,xtype
,case xtype
when 'p' then 'procedure'
when 'fn' then 'function'
when 'if' then 'inline table function'
when 'x' then 'extended proc'
when 'tf' then 'table function'
when 'ft' then 'clr table function'
when 'fs' then 'clr function'
when 'pc' then 'clr proc'
when 'af' then 'clr aggregate function'
when 'u' then 'table'
when 'v' then 'view'
when 'sn' then 'synonym'
end as object_type 
,0
from sys.dm_sql_referenced_entities(@object,'object') e
join sysobjects on e.referenced_id=id;
end
set nocount off



set nocount on;
declare @root int
select @root=objectid from #tree where parentid is null;

with btree (objectid,parentid,objectname,objecttype,level)
as
(
select objectid,parentid,objectname,objecttype,0 as level from #tree
where parentid is null
union all
select b1.objectid,b1.parentid,b1.objectname,b1.objecttype,level+1 from #tree b1 join btree b2 on b2.objectid=b1.parentid 
)


select objectid,parentid,objectname,objecttype,min(level) level into #t from btree 
group by objectid,parentid,objectname,objecttype

update t1 set objectname='*'+t1.objectname
from #t t1 join (select objectname from #t group by objectname having count(*)>1) t2 
on t1.objectname=t2.objectname

declare @stack table(d int identity(1,1),node int)
declare @parent int,@nodename varchar(64),@level  varchar(2),@parent_level varchar(2),@bid int,@objecttype varchar(32),@xml varchar(max)

set @parent=@root
select @nodename=objectname,@level=level,@objecttype=objecttype from #t where objectid =@root
set @xml='<object name="'+@nodename+'" type="'+@objecttype+'">'

delete #t where objectid =@root
insert @stack values(@root)

while exists(select top 1 node from @stack)
begin
while exists (select objectid from #t where parentid=@parent)
begin

select top 1 @bid=objectid,@nodename=objectname,@objecttype=objecttype,@level=level,@parent_level=@level-1 from #t 
where parentid=@parent order by parentid

set @xml=@xml+'<object name="'+@nodename+'" type="'+@objecttype+'">'
if @parent_level=@level 
set @xml=@xml+'</object>'
delete #t where objectid=@bid and level=@level
insert @stack values(@bid)
set @parent=@bid
end
delete @stack where d in (select max(d) from @stack)
select top 1 @parent=node from @stack order by d desc
set @xml=@xml+'</object>'
end
declare @xml_result xml
select @xml_result=@xml
select @xml_result


drop table #t

return
end

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating