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

-- 查询指定表是否有约束，如果有约束就先关闭约束，生成关闭约束的SQL语句，和 恢复外键约束的SQL语句


SET NOCOUNT ON;


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


PRINT '===================== 禁用外键约束 NOCHECK =====================';
-- 移除DISTINCT，无报错
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);


PRINT CHAR(10) + '===================== 恢复外键约束 CHECK =====================';
-- 移除DISTINCT，无报错
SELECT
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] CHECK CONSTRAINT [' + fk.name + '];' AS EnableFK_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);