如何取消在SQL Server 2000中的一个表中的所有外键约束？(How do I drop a

如何退出使用T-SQL在SQL Server 2000中的一个表中的所有外键约束？

Answer 1:

如果简单地禁用的约束是一个不错的选择，你可以使用：

ALTER TABLE myTable NOCHECK CONSTRAINT all

然后您可以切换回上简单地使用：

ALTER TABLE myTable WITH CHECK CHECK CONSTRAINT all

如果你愿意，你可以使用所有表中禁用约束：

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

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

更多的问题：能外键约束暂时使用TSQL禁用？

但是，如果你需要删除永久的约束，你可以使用这个脚本张贴在databasejurnal.com 。

只是修改它稍微下降仅外键

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

Answer 2:

在这里你去：（SQL2000上没有测试，但是应该是确定）

产生“禁用”：

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

产生“使”：

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

更新：哎呀，我还以为你想要它:)你可以只修改上面的一个表中的所有表。