usp_dependencytree

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