如何在 Sql Server 2000 中删除表上的所有外键约束? [英] How do I drop all foreign-key constraints on a table in Sql Server 2000?

问题描述

如何使用 T-SQL 在 SQL Server 2000 中删除表上的所有外键约束?

How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL?

推荐答案

如果在这里简单地禁用约束是一个选项，您可以使用:

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"

更多问题:外键约束是否可以暂时禁用使用 TSQL?

但如果您需要永久删除约束，您可以使用 此脚本发布在 databasejurnal.com 上.

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

这篇关于如何在 Sql Server 2000 中删除表上的所有外键约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！