将所有外键放在一个表中 [英] Drop all foreign keys in a table

问题描述

我有这个脚本，在SQL Server 2005中工作

   -  t-sql scriptlet删除表上的所有约束
 DECLARE @database nvarchar（50）
 DECLARE @table nvarchar（50）
 $ b $ set @database ='dotnetnuke'
 set @table ='tabs'
 
 DECLARE @sql nvarchar（255）
选择@sql = 'ALTER TABLE'+ @table +'DROP CONSTRAINT'+ CONSTRAINT_NAME 
 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 where constraint_catalog = @database and 
 table_name = @table 
 exec sp_executesql @sql 
 END 
  

它在SQL Server 2008中不起作用。如何轻松删除所有外键约束为某个表？有没有人有更好的脚本？

解决方案

有一个更简单的方法。
为什么呢？
脚本：

$ select $ alter table'+ schema_name（Schema_id）+'。'+ object_name（parent_object_id ）
+'DROP CONSTRAINT'+来自sys.foreign_keys的名字f1`

删除所有外键

I had this script which worked in sql server 2005

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

It does not work in SQL Server 2008. How can I easily drop all foreign key constraints for a certain table? Does anyone have a better script?

解决方案

There an easier method. Why all that? The script:

`select ' alter table ' + schema_name(Schema_id)+'.'+ object_name(parent_object_id)
+ '  DROP CONSTRAINT  ' +  name   from sys.foreign_keys f1`

Will script the drop for all foreign keys

这篇关于将所有外键放在一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！