為什麼從腳本中刪除所有外鍵不起作用？

我在 SQL Server 2008 R2 機器上擁有系統管理員權限。我找到了一個腳本，它可以刪除所有外鍵並在測試導入後需要時為我們重新添加它們。所以我執行下面的程式碼沒有語法錯誤，因為它顯示了“Alter Table

$$ ParentTable $$Drop Constraint FK_Name"… 總共 23 個鍵，但是當我刷新伺服器或數據庫和表時，當我為每個表展開鍵節點時我仍然看到它們？那為什麼不刪除它們？還有，如果我想再次執行它以進行測試，是否需要將其轉換為永久表或將腳本放入儲存過程中？請指教。 我使用了“Pinal Dave”發布的“Swastik Mishra”中的以下腳本 http://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-數據庫創建所有外鍵約束在數據庫中/

   SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '

GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)

GO’
FROM
@table
GO

這是一個更好的腳本。

1. 它不使用可怕的INFORMATION_SCHEMA意見。除其他外，這些視圖不會針對唯一約束公開外鍵；僅針對顯式主鍵約束。
2. 它不會GO放在 T-SQL 中，如果您動態執行命令，它將不起作用（因為GO它是 SSMS 等互動式工具的批處理分隔符，而不是 T-SQL 關鍵字）。
3. 它沒有錯誤地複制撇號來代替單引號（’vs. '）。
4. 它處理多列外鍵約束（您現在擁有的程式碼假定所有外鍵僅針對單個列定義）。

其餘部分摘自我的部落格文章，分組連接的四個實際案例。這是腳本#4。

---

下降很容易；只需從 sys.foreign_keys 建構一個簡單的 ALTER TABLE 命令列表（我還添加了註釋掉的過濾器，可用於將範圍限制為匹配命名模式或存在於特定模式中的引用表）：

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
-- WHERE fk.referenced_object_id IN 
-- (SELECT [object_id] FROM sys.tables WHERE name LIKE N'%some pattern%')
-- WHERE fk.referenced_schema_id IN 
-- (SELECT [schema_id] FROM sys.schemas WHERE name = N'some_schema')

PRINT @drop;

不要執行那個！當然，您需要在 CREATE 命令仍然存在時生成它們。創建稍微複雜一些。我們需要生成約束兩側的列列表，即使在大多數情況下只有一列（多列情況是分組連接非常方便的地方）：

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE ' 
  + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
  + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
  + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   FROM sys.columns AS c 
   INNER JOIN sys.foreign_key_columns AS fkc 
   ON fkc.parent_column_id = c.column_id
   AND fkc.parent_object_id = c.[object_id]
   WHERE fkc.constraint_object_id = fk.[object_id]
   ORDER BY fkc.constraint_column_id 
   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
 + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
 + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   FROM sys.columns AS c 
   INNER JOIN sys.foreign_key_columns AS fkc 
   ON fkc.referenced_column_id = c.column_id
   AND fkc.referenced_object_id = c.[object_id]
   WHERE fkc.constraint_object_id = fk.[object_id]
   ORDER BY fkc.constraint_column_id 
   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
 ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
 ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
--WHERE rt.name LIKE N'%some pattern%'
--WHERE rs.name = N'some_schema'

PRINT @create;

當您對輸出感到滿意時（請記住，PRINT 限制為 8K，因此它可能看起來像命令被截斷），將其添加到末尾，然後再次執行：

EXEC sys.sp_executesql @drop;

-- drop and re-create the table here, whatever that needs to entail

EXEC sys.sp_executesql @create;

引用自：https://dba.stackexchange.com/questions/77018