January 2, 2008 at 3:29 pm
I would like to programmatically drop/truncate and recreate tables from time to time. When a table has a foreign key constraint, the constraints have to be removed before dropping the table. I have a script that can get me the names of all the foreign keys for a table so that I can drop them. I am having trouble creating the script that will give me the information about each foreign key so that I can add back the foreign keys after dropping & recreating or truncating the table?
Thanks in advance for your help,
Liz
January 2, 2008 at 3:50 pm
I use this:
/* Generates script for recreating foreign key constraints. Run BEFORE running
DROP statements, that is, while the foreign key constraints still exist.
WARNING: this script does not pickup the ON UPDATE/DELETE CASCADE clause, if present.
These clauses could be important for the functioning of a database!
*/
select 'ALTER TABLE ' + cast(t.name as char(40)) + 'ADD CONSTRAINT '
+ cast(c.name as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '
+ cast(rt.name as char(30)) + '(' + rc.name + ') ' + char(13)
from (select distinct constid, fkeyid, rkeyid, fkey, rkey from sysforeignkeys) as FK
join sysobjects c on c.id = FK.constid
join sysobjects t on t.id = FK.fkeyid
join sysobjects rt on rt.id = FK.rkeyid
join syscolumns fc on fc.colid = FK.fkey and fc.id = FK.fkeyid
join syscolumns rc on rc.colid = FK.rkey and rc.id = FK.rkeyid
Greg
Greg
January 2, 2008 at 4:05 pm
Thanks - how would i use this if there are foreign keys constructed of multiple fields?
January 4, 2008 at 9:55 am
Hmmm, I'm not sure since sysforeignkeys stores each component column as a seperate row. I'll to some experimenting and post back.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply