如何在Sql Server 2000中的表上删除所有外键约束？

解决方法

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"

更多的问题：Can foreign key constraints be
temporarily disabled using TSQL?

但如果您需要永久删除约束,您可以使用this script posted on 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!