Habilitar o Deshabilitar Llaves Foráneas en SQL Server


Con este script se habilitan o deshabilitan todos los foreing keys de la Base de Datos que se tenga seleccionada.

DECLARE @cFKName    VARCHAR(50)
DECLARE @cTableName    VARCHAR(50)

SET NOCOUNT ON;

DECLARE Cur_FK CURSOR LOCAL FORWARD_ONLY FOR        
    SELECT SFK.NAME AS FK_NAME, (SCH.NAME + '.' + SAO1.NAME) AS TABLE_NAME
    FROM SYS.FOREIGN_KEYS AS SFK,
    SYS.SCHEMAS AS SCH,
    SYS.ALL_OBJECTS AS SAO1
    WHERE SFK.SCHEMA_ID = SCH.SCHEMA_ID
    AND SFK.PARENT_OBJECT_ID = SAO1.OBJECT_ID

OPEN Cur_FK
FETCH Cur_FK INTO @cFKName, @cTableName

WHILE (@@FETCH_STATUS = 0)
BEGIN

    BEGIN TRY
        -- Para Habilitar
        EXEC ('ALTER TABLE ' + @cTableName + ' CHECK CONSTRAINT ' +  @cFKName)
        -- Para Deshabilitar
        EXEC ('ALTER TABLE ' + @cTableName + ' NOCHECK CONSTRAINT ' +  @cFKName)
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH

    FETCH Cur_FK INTO @cFKName, @cTableName

END -- Fin del bucle WHILE

CLOSE Cur_FK
DEALLOCATE Cur_FK

Scroll al inicio