May 5, 2003 at 9:18 am
Is there a proc or method by which all FK's can be removed for all the USER tables in a given databases. The problem is the there are about 70 databases and we need to drop all the FK relationships. Does anyone have some script to do that.
Thanks.
May 5, 2003 at 10:59 am
One alternative is to use sysobjects
select OBJECT_NAME(parent_obj), name
from sysobjects
where xtype = 'F'
This gives the list of table name and associated foreign keys. You can declare a cursor to loop through each of them and drop them.
To loop through the databases you can use master..sysdatabases system table.
May 5, 2003 at 11:07 am
Here is a modification of the script above to create a batch that would do the work. You would then apply the batch to the given database:
select 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' DROP CONSTRAINT ' + name + CHAR(13) + 'GO' + CHAR(13)
from sysobjects
where xtype = 'F'
May 5, 2003 at 11:27 am
Another work around.
declare @foreignkey sysname
declare @referencingtable sysname
declare @cmd sysname
DECLARE fkey_cursor CURSOR FOR SELECT object_name(constid), object_name(fkeyid) from sysreferences
OPEN fkey_cursor
FETCH NEXT FROM fkey_cursor INTO @foreignkey, @referencingtable
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = 'alter table ' + @referencingtable + ' drop constraint ' + @foreignkey
print @cmd
exec (@cmd)
FETCH NEXT FROM fkey_cursor INTO @foreignkey, @referencingtable
END
CLOSE fkey_cursor
DEALLOCATE fkey_cursor
GO
May 5, 2003 at 12:23 pm
Thanks for all answers :))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply