Technical Article

usp_dependencytree

,

This procedure shows in a tree structure the dependencies of an object. It's very a basic, but effective script. Script is developed for 2K5

Make sure recursive triggers is enabled!

Also, in order to be sure the metadata in sys.sql_dependencies is actual, run sp_refreshview for views, or sp_refreshsqlmodule for each view/procedure and function

if object_id('usp_dependencytree') is not null
   drop procedure usp_dependencytree
go

create procedure usp_dependencytree(@ObjectName sysname, @deplevel int)
/*
    name:    usp_dependencytree
    author:  wilfred van dijk (www.wilfredvandijk.nl)
    purpose: shows dependencytree for specified object

    date:    20080625 initial release
*/as
  begin
    declare @dependson sysname
    declare @soort varchar(32)

    select @soort = type_desc from sys.objects where name = @ObjectName

    if @deplevel = 0
      print @ObjectName + ' (' + @soort + ')'
    else
      if @deplevel = 1
        print '+-' + @ObjectName+ ' (' + @soort + ')'
      else
        print replicate('| ', @deplevel-1) + '+-' + @ObjectName+ ' (' + @soort + ')'


    declare c_lus cursor local for
      select distinct(object_name(REFERENCED_MAJOR_ID))
      from sys.sql_dependencies
      where object_id = object_id(@ObjectName)

    open c_lus
    fetch next from c_lus into @dependson

    while @@fetch_status = 0
      begin
        set @deplevel = @deplevel + 1
        exec usp_dependencytree @dependson, @deplevel
        set @deplevel = @deplevel - 1
        fetch next from c_lus into @dependson
      end
    close c_lus
    deallocate c_lus


end
go

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating