問題描述

我想刪除所有具有以下條件的外鍵.

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'

如果您創(chuàng)建一個動態(tài)查詢(用于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.

這篇關于如何從 SQL Server 數據庫中刪除所有外鍵?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！