2008-01-04 (first published: 2003-09-26)
185,251 reads
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