if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation AS print 1'

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation1 AS print 1'

go

alter procedure usp_DisplayTableRelation

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

as

begin -- sp

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##UsedTableName' )

create table ##UsedTableName (TableName nvarchar(128))-- use to track recursive calls to same table

else delete from ##UsedTableName

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

create table ##DisplayTableRelation (Relation nvarchar(4000))-- use to track recursive calls to same table

else delete from ##DisplayTableRelation

exec usp_DisplayTableRelation1 @table_name = @table_name

select * from ##DisplayTableRelation

drop table ##UsedTableName

drop table ##DisplayTableRelation

return

end --sp

go

alter procedure usp_DisplayTableRelation1

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

, @space_len int = -4 output -- for insert into ##DisplayTableRelation (Relation) selecting position purpose

as

begin -- sp

declare @child nvarchar(128)

declare @usedTable nvarchar(100)

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

begin

exec usp_DisplayTableRelation @table_name

return

end

set @space_len = @space_len + 4

if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )

begin -- leaf level

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name

set @space_len = @space_len - 4

return

end -- leaf level

else -- the @table_name table exists

begin -- else

set @usedTable = N''

if exists (select * from ##UsedTableName where TableName = @table_name)

set @usedTable = N' (Loop Reference)'

else insert into ##UsedTableName (TableName) select @table_name

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name + @usedTable

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name + @usedTable

if (@usedTable = N'' --first time

and exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid)) -- self referenced

insert into ##DisplayTableRelation (Relation) select space(@space_len+4) + '|---' + @table_name

declare curChild cursor local for

select object_name(fkeyid) as child from sysforeignkeys

where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

open curChild

fetch next from curChild into @child

while @@fetch_status = 0

begin -- cursor loop

if (@usedTable = N'') exec usp_DisplayTableRelation1 @table_name = @child, @space_len = @space_len output

fetch next from curChild into @child

end -- cursor loop

close curChild

deallocate curChild

end --else

set @space_len = @space_len - 4

return

end --sp