Using Metadata to Solve Complex Problems

s someone who genuinely likes SQL and relational databases, I get a particular kick out of seeing how database engines use relational tables themselves to store their own metadata. As an example, we are all familiar with the sysobjects table, which contains a row for each object in the database.

Understanding the metadata is of more than theoretical interest. It can be used to solve complex problems. Recently, I was given a database with over a hundred tables to look at. Of course, almost no documentation existed. I quickly needed to chart a path through the database. I wanted to build a list of all the tables, starting from the base tables on down.

The sysreferences table contains a row for every foreign key in the database. I can therefore obtain a list of all tables that don't have any foreign keys and, hence, serve as the "base" of the database using the following code:

select name
from sysobjects obj
where
not exists
(select fkeyid from sysreferences where fkeyid = id) and
type = 'U'

The trick is to find the next level of tables. For the next level, I only need to find any tables that have foreign key references to the tables listed above, and only those tables. This logic loop continues until I have found all the tables.

Here is the code for the whole procedure. (The code in this 10-Minute Solution was written to work equally well with SQL Server 6.5. It therefore does not use any of the INFORMATION_SCHEMA views.)

set nocount on

create table #build_order
(
   rank int,
   id int,
   name char(50) null
   
)

declare @rank int, @notfinished int
select @rank = 0, @notfinished = 1

--get tables that have no foreign key

insert into #build_order(rank,id,name) 

select @rank,id ,name
from sysobjects obj
where
not exists
(select fkeyid from sysreferences where fkeyid = id) and
type = 'U'

while (@notfinished <> 0)
begin
   select @rank = @rank + 1   
   --get tables that have a foreign key reference to 
   --tables already in our build order table.
   insert into #build_order(rank,id)
      select    distinct @rank,sr.fkeyid
      from sysreferences sr, #build_order bo
      where
      sr.rkeyid = bo.id and
      bo.rank = @rank - 1 
             and 
      not exists --but don't include tables 
	             --that also reference other tables not 
		         --yet in our build order table.
      (select * from sysreferences sr1
         where sr1.fkeyid = sr.fkeyid and 
            sr1.rkeyid not in(select id from #build_order 
                     where rank <= @rank -1)
      )

   select @notfinished = @@rowcount

end

select rank,object_name(id) as Name
from #build_order
order by rank,name

drop table #build_order

In my next article, I begin looking at the metadata accessible through the INFORMATION_SCHEMA views.