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
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.