[ACCEPTED]-How do I drop all foreign-key constraints on a table in Sql Server 2000?-tsql

Accepted answer
Score: 14

If simply disabling constraints is an option 7 here, you can use:

ALTER TABLE myTable NOCHECK CONSTRAINT all

then you can switch them 6 back on simply using:

ALTER TABLE myTable WITH CHECK CHECK CONSTRAINT all

If you want to disable 5 constrains in all tables you can use:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

More 4 in the question: Can foreign key constraints be temporarily disabled using TSQL?

But if you need to drop 3 constraints permanently you can use this script posted on databasejurnal.com.

Just 2 modify it slightly to only drop the foreign 1 keys

create proc sp_drop_fk_constraints
    @tablename  sysname
as
-- credit to: douglas bass

set nocount on

declare @constname  sysname,
    @cmd        varchar(1024)

declare curs_constraints cursor for
    select  name
    from    sysobjects 
    where   xtype in ('F')
    and (status & 64) = 0
    and     parent_obj = object_id(@tablename)

open curs_constraints

fetch next from curs_constraints into @constname
while (@@fetch_status = 0)
begin
    select @cmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constname
    exec(@cmd)
    fetch next from curs_constraints into @constname
end

close curs_constraints
deallocate curs_constraints

return 0
Score: 4

Here you go: (not tested on SQL2000, but 4 should be ok)

Generates 'disables':

SELECT  'IF EXISTS (SELECT * FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N''[dbo].' + FK +''') 
   AND parent_object_id = OBJECT_ID(N''[dbo].' + PT + ''')) 
   ALTER TABLE ' + PT + ' NOCHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

Generates 3 'enables':

SELECT  'ALTER TABLE ' + PT + ' WITH CHECK CHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

Update: Oops, I thought you wanted 2 it for all tables :) You can just modify 1 above for your single table.

Score: 2

I think you'll find that there is no easy 8 way to drop constraints on a table in SQL 7 Server 2000. That said, there are plenty 6 of people who have written scripts that 5 can identify and remove/disable/recreate 4 foreign key constraints. One example is 3 at http://www.mssqltips.com/tip.asp?tip=1376 - but I haven't tested it on SQL Server 2 2000.

EDIT: Here is another example that generates 1 drop/create scripts for you.

More Related questions