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
