Pages

Monday, April 9, 2012

How to enable and disable the Foreign Key constraints in MS-SQL

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 

No comments:

Post a Comment