如何从 SQL Server 数据库中删除所有外键?

2021-09-10 00:00:00 sql tsql sql-server

我想删除所有具有以下条件的外键.

I want to drop the all foreign keys that have the following conditions.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME IN ('Table1', 'Table2')
  AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

推荐答案

有一个名为 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 的表，它存储了所有表的约束.FOREIGN KEY 的约束类型也保留在该表中.因此，通过这种类型的过滤，您可以访问所有外键.

There is a table named INFORMATION_SCHEMA.TABLE_CONSTRAINTS which stores all tables constraints. constraint type of FOREIGN KEY is also keeps in that table. So by filtering of this type you can reach to all foreign keys.

SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

如果您创建一个动态查询(用于DROP-ing 外键)以更改表，则可以达到更改所有表的约束的目的.

If you create a dynamic query (for DROP-ing the foreign key) in order to alter the table, you can reach to the aim of altering the constraints of all tables.

WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%'))
BEGIN
    DECLARE @sql_alterTable_fk NVARCHAR(2000)

    SELECT  TOP 1 @sql_alterTable_fk = ('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND TABLE_NAME IN ('Table1', 'Table2')
            AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

    EXEC (@sql_alterTable_fk)
END

EXISTS 函数及其参数确保至少有一个外键约束.

EXISTS function with its parameter assures that there is at least one constrain for foreign key.

相关文章