SQLServer Table Heirarchy List

[conraba]

Does anybody have a script to retreive tablenames FK dependencies, making it helpful in determining the hierarchy of tables in a database?

 

[LFCfan]

Hi,
Apologies if you've already sorted this, but system stored proc sp_fkeys might be of help...?

 

[conraba]

I wanted mainly to get the table hierarchy on a database considering the constrains that are in-place. I found this script however, it chokes out when running it on a large database of about 260+ tables. Anybody has an idea, I will greatly appreciate it.

===========================================================
set nocount on

declare @Rows int,
@LevelID int
declare @Tables TABLE (TableName varchar(200),
LevelID int)

insert into @Tables (TableName, LevelID)
select name, 1
from sysobjects
where xtype= 'U'

select @Rows = @@RowCount, @LevelID = 1

while @Rows > 1 begin
update @Tables
set LevelID = LevelID + 1
where TableName in (select distinct master.name
from sysobjects master, sysobjects ref, sysreferences refkey
where refkey.fkeyid = ref.id
and refkey.rkeyid = master.id
and ref.Name in (select distinct TableName from @Tables where LevelID = @LevelID))
select @Rows = @@RowCount
select @LevelID = @LevelID + 1
end

select *
from @Tables
order by 2 desc,1

===========================================================