阮胜昌的博客-Linux&MySQL数据库运维

-- 查询指定表是否有约束，如果有约束就先关闭约束，生成删除约束的SQL语句，和重新添加约束的语句

SQLServer2012 完整脚本：批量生成【禁用 FK / 删除 FK / 重建 FK】
说明
仅处理外键 FOREIGN KEY（主键 / 唯一 / Check 约束不处理）；
输出三段脚本：
① 临时关闭校验（NOCHECK，仅跳过新增校验，不删约束）
② 彻底删除外键约束（DROP CONSTRAINT，用于 TRUNCATE 主表）
③ 重建恢复外键约束（ADD CONSTRAINT，清空数据后还原关联）
内置你全部表清单，无 DISTINCT 规避 145 报错，兼容大小写表名、dbo 架构；
区分：NOCHECK 只是临时关闭校验，DROP 才会删除约束解决 TRUNCATE 报错。
 

SET NOCOUNT ON;


-- 1. 载入目标表清单
DECLARE @TableList TABLE(TableName NVARCHAR(200));
INSERT INTO @TableList(TableName) VALUES
('shukuinfo123'),
('shukuinfo123StoreOrder'),
('shukuinfo123_Update');


-- ========== 1、临时关闭外键校验（NOCHECK，不删除约束） ==========
PRINT CHAR(10)+'===== 【1 临时关闭外键校验 NOCHECK CONSTRAINT】 =====';
SELECT
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] NOCHECK CONSTRAINT [' + fk.name + '];' AS DisableFK_SQL
FROM sys.foreign_keys fk
INNER JOIN @TableList tl ON OBJECT_NAME(fk.parent_object_id) = tl.TableName
ORDER BY OBJECT_NAME(fk.parent_object_id);


-- ========== 2、删除外键约束 DROP（解决TRUNCATE报错） ==========
PRINT CHAR(10)+'===== 【2 删除外键约束 DROP CONSTRAINT】 =====';
SELECT
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + '];' AS DropFK_SQL
FROM sys.foreign_keys fk
INNER JOIN @TableList tl ON OBJECT_NAME(fk.parent_object_id) = tl.TableName
ORDER BY OBJECT_NAME(fk.parent_object_id);


-- ========== 3、重建外键约束 ADD（彻底修复4104标识符绑定报错） ==========
PRINT CHAR(10)+'===== 【3 重建恢复外键约束 ADD CONSTRAINT】 =====';
SELECT
'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] WITH CHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('
+ STUFF((
    SELECT ',[' + col_name(fkc2.parent_object_id,fkc2.parent_column_id) + ']'
    FROM sys.foreign_key_columns fkc2
    WHERE fkc2.constraint_object_id = fk.object_id
    ORDER BY fkc2.constraint_column_id
    FOR XML PATH('')
),1,1,'')
+ ') REFERENCES [' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + ']('
+ STUFF((
    SELECT ',[' + col_name(fkc2.referenced_object_id,fkc2.referenced_column_id) + ']'
    FROM sys.foreign_key_columns fkc2
    WHERE fkc2.constraint_object_id = fk.object_id
    ORDER BY fkc2.constraint_column_id
    FOR XML PATH('')
),1,1,'')
+ ');' AS CreateFK_SQL
FROM sys.foreign_keys fk
INNER JOIN @TableList tl ON OBJECT_NAME(fk.parent_object_id) = tl.TableName
ORDER BY OBJECT_NAME(fk.parent_object_id);