删除一个模式中的一组表和另一个模式中的另一组表之间的所有关系的T-SQL脚本?

使用元数据:

SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

添加筛选条件以查找要删除的约束,然后将其插入模板中:

ALTER TABLE {TABLE_SCHEMA}.{TABLE_NAME} DROP {CONSTRAINT_NAME}

请通过复制并粘贴到SQL Studio来使用以下脚本:

SELECT  'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP [' + CONSTRAINT_NAME + ']'
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

select * from sys.objects o
join sys.schemas s on o.schema_id = s.schema_id
where o.type = 'F'

之后删除如下内容

ALTER TABLE {TABLE_SCHEMA}.{TABLE_NAME} DROP {CONSTRAINT_NAME}

我发誓这不会在SQL2005上起作用,因为我没有一个实例来测试它,但如果有,它将使它在SSMS中成为一个简单的复制/粘贴作业。我将让您迭代结果并执行,如果这是您想要的。

将schema_1和schema_2替换为您试图查找它们之间关系的schema名称。

declare @s1 int
declare @s2 int

set @s1 = schema_id( 'schema_1' )
set @s2 = schema_id( 'schema_2' )

select
     N'alter table [' + s.name + N'].[' + o_p.name + N'] drop constraint [' + fk.name + N']'
from sys.foreign_keys fk
join sys.schemas s on ( s.schema_id = fk.schema_id )
join sys.objects o_p on ( o_p.object_id = fk.parent_object_id )
join sys.objects o_r on ( o_r.object_id = fk.referenced_object_id )
where
        ( o_p.schema_id = @s1 and o_r.schema_id = @s2 )
    or  ( o_p.schema_id = @s2 and o_r.schema_id = @s1 )