Pages

Showing posts with label enable. Show all posts
Showing posts with label enable. Show all posts

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