Finding Foreign Keys in MS SQL 2000

July 5, 2007

I’ve been asked to perform an upgrade for one of our vendors to a new version of our software. Unfortunately their data is still stored in the old DB format, and app pages. We figured the easier way to perform the upgrade would be to migrate their data to the new format, rather than trying to upgrade their existing version of the application.

Our plan of action involved taking an existing copy of the DB, stripping out any data, and then copying over the new data. The easiest way to clean up the data is to use truncate table tablenamehere, and away goes the data. This is fine for most databases, however our developers have used foreign keys. This means that it specifically references data in other tables, if it’s not there, then it’ll get upset about it. This constraint stops you from issuing a truncate because it breaks all the relationships in the database. SQL simply throws a nice error message like this:

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'sometable' because it is being referenced by a FOREIGN KEY constraint.

This obviously isn’t very helpful because it’s quite difficult to figure out which table references the table you’re trying to truncate. So I had to dig a little bit into Microsoft’s “sys tables”. Each database has a collection of sys tables. In these tables, a bunch of information about the databases tables, columns, indexes, and other objects is contained. From playing around, I built the following script:

select
  sod.name as [dst_table],
  so.name as [dst_key],
  scs.name as [dst_col],
  sos.name as [src_table],
  sc.name as [src_col]
from
  sysobjects so (nolock)  
    inner join sysreferences sr (nolock) on sr.constid = so.id
    inner join syscolumns scs (nolock) on scs.id = so.parent_obj and scs.colid = so.info
    inner join sysobjects sod (nolock) on sod.id = so.parent_obj
    inner join sysobjects sos (nolock) on sos.id = sr.rkeyid
    inner join syscolumns sc (nolock) on sc.id = sos.id and sc.colid = sr.rkeyindid
where
  sr.rkeyid = object_id(sometable)

This code returns the destination table for the foreign key, the foreign key name, the field that the constraint is placed against, and the reference table, and column. From the results in this, it’s relatively easy to drop all the foreign keys with a small cursor, something like this:

select
  sod.name as [dst_table],
  so.name as [dst_key],
  scs.name as [dst_col],
  sos.name as [src_table],
  sc.name as [src_col]
into ##tmpFKs
from
  sysobjects so (nolock)  
    inner join sysreferences sr (nolock) on sr.constid = so.id
    inner join syscolumns scs (nolock) on scs.id = so.parent_obj and scs.colid = so.info
    inner join sysobjects sod (nolock) on sod.id = so.parent_obj
    inner join sysobjects sos (nolock) on sos.id = sr.rkeyid
    inner join syscolumns sc (nolock) on sc.id = sos.id and sc.colid = sr.rkeyindid
where
  sr.rkeyid = object_id(sometable)


declare @sql varchar(255),
		@tblName varchar(255),
		@fkName varchar(255)

declare curStripKeys cursor local
for
  select dst_table, dst_key from ##tmpFKS
open curStripKeys

fetch from curStripKeys
into @tblName, @fkName
while @@fetch_status = 0
  begin

    set @sql = 'alter table [' + @tblName + '] drop constraint [' + @fkName + ']'

    print @sql

    fetch next from curStripKeys
    into @tblName, @fkName
  end
close curStripKeys
deallocate curStripKeys

Now, if you’re sneaky, and want to rebuild all this foreign keys afterwards, instead of inserting them into a temporary table, you can put them in a more static table, at least until you’ve cleaned up all the tables. Once you’ve cleaned up the tables, you can reverse the drop, with another cursor… like this:

declare @sql varchar(255),
        @tblName varchar(255),
        @fkName varchar(255),
        @dstField varchar(255),
        @srcTable varchar(255),
        @srcField varchar(255)

declare curAddKeys cursor local
for
  select dst_table, dst_key, dst_col, src_table, src_col from ##tmpFKs

open curAddKeys
fetch from curAddKeys
into @tblName, @fkName, @dstField, @srcTable, @srcField
while @@fetch_status = 0
  begin

    set @sql = 'alter table [dbo].[' + @tblName + '] with nocheck add constraint \
      [' + @fkName + '] foreign key([' + @dstField + ']) references \
      [dbo].[' + @srcTable + '] ([' + @srcField + '])'

    print @sql

    fetch next from curAddKeys
    into @tblName, @fkName, @dstField, @srcTable, @srcField
  end
close curAddKeys
deallocate curAddKeys

In both examples, I’ve printed the output to screen, rather than issued an exec, but it gives you a few moments to review the output, and make sure it’s doing what you expect it to be.

All a bit of fun and games with system tables, and cursors.

Technorati Tags: Work, SQL, Microsoft, Tech

Posted in Microsoft, Technology, Work