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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

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.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date