The initial script creates the view called sysfkeys and then runs the following scripts in Query Analyzer. The second script will generate a DROP foreign key constraint command against each table for a particular database, while the final script will generate an ADD foreign key constraint command for all user tables for a particular database.
/* First create the sysfkey view object */
CREATE view sysFKeys as
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
from sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type = 'F'
/* Drop foreignkey constraint from table */
select 'ALTER TABLE ' + foreign_table + '
DROP CONSTRAINT ' + foreign_key_name
from sysfkeys order by foreign_table
/* ADD foreignKey constraints to a table */
select 'ALTER TABLE ' + foreign_table + ' ADD CONSTRAINT ' + foreign_key_name + '
FOREIGN KEY (' + foreign_column_1 + ')
REFERENCES ' + primary_table + ' (' + primary_column_1 + ')'
from sysfkeys order by foreign_table
.gif){kind=spacer}
Technical Documents >> 
RELATED ARTICLES
