RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Using Metadata to Solve Complex Problems

The article describes a basic understanding of the metadata in a database. Though theoretical in nature, it can be used 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
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
not exists
(select fkeyid from sysreferences where fkeyid = id) and
type = 'U'

while (@notfinished <> 0)
   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
      sr.rkeyid = bo.id and
      bo.rank = @rank - 1 
      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


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.

Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date