CREATE PROCEDURE metadata.sp_enabledisableforeignkeys
@ownerName NVARCHAR(128),
@tableName NVARCHAR(128),
@enabled NVARCHAR(1)
AS
BEGIN
DECLARE @CurrentSchema NVARCHAR(300),
@CurrentTable NVARCHAR(300),
@CurrentFKName NVARCHAR(300)
DECLARE @executeSql NVARCHAR(2000),
@enableSql NVARCHAR(20)
IF @enabled = 'Y'
SET @enableSql = ' CHECK'
ELSE
SET @enableSql = ' NOCHECK'
DECLARE fklist CURSOR FOR
SELECT sysschemas.name AS schemaname,
systables.name AS tablename,
sysfks.name AS fkname
FROM sys.foreign_keys sysfks
INNER JOIN sys.tables systables
ON systables.object_id = sysfks.parent_object_id
INNER JOIN sys.schemas sysschemas
ON sysschemas.schema_id = systables.schema_id
INNER JOIN sys.tables sysreftables
ON sysreftables.object_id = sysfks.referenced_object_id
INNER JOIN sys.schemas sysrefschemas
ON sysrefschemas.schema_id = sysreftables.schema_id
WHERE sysreftables.name = @tableName
AND sysrefschemas.name = @ownerName
OPEN fklist
FETCH NEXT FROM fklist INTO @CurrentSchema, @CurrentTable, @CurrentFKName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @executeSql = 'Alter Table ' + @CurrentSchema + '.' +
@CurrentTable + @enableSql + ' CONSTRAINT ' + @CurrentFKName
EXEC Sp_executesql @executeSql
FETCH NEXT FROM fklist INTO @CurrentSchema, @CurrentTable,
@CurrentFKName
END
CLOSE fklist
DEALLOCATE fklist
END
@ownerName NVARCHAR(128),
@tableName NVARCHAR(128),
@enabled NVARCHAR(1)
AS
BEGIN
DECLARE @CurrentSchema NVARCHAR(300),
@CurrentTable NVARCHAR(300),
@CurrentFKName NVARCHAR(300)
DECLARE @executeSql NVARCHAR(2000),
@enableSql NVARCHAR(20)
IF @enabled = 'Y'
SET @enableSql = ' CHECK'
ELSE
SET @enableSql = ' NOCHECK'
DECLARE fklist CURSOR FOR
SELECT sysschemas.name AS schemaname,
systables.name AS tablename,
sysfks.name AS fkname
FROM sys.foreign_keys sysfks
INNER JOIN sys.tables systables
ON systables.object_id = sysfks.parent_object_id
INNER JOIN sys.schemas sysschemas
ON sysschemas.schema_id = systables.schema_id
INNER JOIN sys.tables sysreftables
ON sysreftables.object_id = sysfks.referenced_object_id
INNER JOIN sys.schemas sysrefschemas
ON sysrefschemas.schema_id = sysreftables.schema_id
WHERE sysreftables.name = @tableName
AND sysrefschemas.name = @ownerName
OPEN fklist
FETCH NEXT FROM fklist INTO @CurrentSchema, @CurrentTable, @CurrentFKName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @executeSql = 'Alter Table ' + @CurrentSchema + '.' +
@CurrentTable + @enableSql + ' CONSTRAINT ' + @CurrentFKName
EXEC Sp_executesql @executeSql
FETCH NEXT FROM fklist INTO @CurrentSchema, @CurrentTable,
@CurrentFKName
END
CLOSE fklist
DEALLOCATE fklist
END