How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL?
I think you'll find that there is no easy way to drop constraints on a table in SQL Server 2000. That said, there are plenty of people who have written scripts that can identify and remove/disable/recreate foreign key constraints. One example is at http://www.mssqltips.com/tip.asp?tip=1376 - but I haven't tested it on SQL Server 2000.
EDIT: Here is another example that generates drop/create scripts for you.
If simply disabling constraints is an option here, you can use:
ALTER TABLE myTable NOCHECK CONSTRAINT all
then you can switch them back on simply using:
ALTER TABLE myTable WITH CHECK CHECK CONSTRAINT all
If you want to disable 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 in the question: Can foreign key constraints be temporarily disabled using TSQL?
But if you need to drop constraints permanently you can use this script posted on databasejurnal.com.
Just modify it slightly to only drop the foreign 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
Here you go: (not tested on SQL2000, but 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 '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 it for all tables :) You can just modify above for your single table.