Table Heirarchy

I always wanted a script which will enable me view the heirarchy of tables bases on the foreign key relationsships. I wrote this script. This works in most of the cases. Still not able to trace the bug why it does not work in all the cases.
set nocount on

/*
drop TABLE Tables
go
Create TABLE Tables (
TableName varchar(200) unique,
LevelID int)
*/

Truncate Table Tables

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

select @LevelID = 1

insert into Tables (TableName, LevelID)
select name, @LevelID
from sysobjects
where xtype= 'U'
and name not in (select distinct object_name(rkeyid) from sysreferences)

while 1=1
begin
select @Rows = count(Name) from Sysobjects
where Name not in (select TableName from Tables)
and xtype= 'U'
if @Rows = 0
break
/*********************************Processing for All Levels*************************************/

declare cur_table cursor local for
select TableName
from Tables
where LevelID = @LevelID
order by TableName

select @LevelID = @LevelID + 1

/*********************************Processing for A particular Level*****************************/
open cur_table
fetch next from cur_table
into @TableName
while (@@fetch_status = 0)
begin
if exists( select Tablename from Tables, sysreferences
where TableName = object_name(rkeyid)
andobject_name(fkeyid) = @TableName)
begin
Update Tables
set LevelID = @LevelID
from sysreferences
where TableName = object_name(rkeyid)
andobject_name(fkeyid) = @TableName
end
else
begin
Insert into Tables (TableName, LevelID)
select object_name(rkeyid), @LevelID
from sysreferences
where object_name(fkeyid) = @TableName
end

if exists( select distinct Name from Tables, sysreferences, sysobjects
where xtype= 'U'
and Name not in (select TableName from Tables)
and Name = object_name(rkeyid)
andobject_name(fkeyid) = @TableName)
begin
Insert into Tables (TableName, LevelID)
select distinct Name, @LevelID
from Tables, sysreferences, sysobjects
where xtype= 'U'
and Name not in (select TableName from Tables)
and Name = object_name(rkeyid)
andobject_name(fkeyid) = @TableName
end
fetch next from cur_table
into @TableName
end
close cur_table
deallocate cur_table
/*********************************Processing for A particular Level*****************************/
end
select *
from Tables
order by 2 desc,1


/*
select Name from Sysobjects
where Name not in (select TableName from Tables (with nolock))
and xtype= 'U'

select object_name(id) ,rows
from sysindexes
where indid < 2
and id in(select object_id(name) from sysobjects
where type = 'u')
order by 2 desc

*/

Gaurav

Not bad! Will require a number of changes though to make it more 'informational' to the average guy. You may think about converting this script into a stored procedure and adding a few checks of existence (like before creating the 'Tables' table).

Nathan H.O.