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.
