Habilitar o Deshabilitar Triggers en SQL Server

Con este script se pueden habilitar o deshabilitar todos los triggers de la Base de Datos que se encuentre seleccionada.

    DECLARE @cTgName    VARCHAR(50)
    DECLARE @cTableName    VARCHAR(50)

    SET NOCOUNT ON;
   
    DECLARE Cur_TG CURSOR LOCAL FORWARD_ONLY FOR
        SELECT (SCH.NAME + '.' + STG.NAME) AS TG_NAME, (SCH.NAME + '.' + SAO.NAME) AS TABLE_NAME 
        FROM SYS.TRIGGERS STG,
        SYS.ALL_OBJECTS AS SAO,
        SYS.SCHEMAS AS SCH
        WHERE STG.PARENT_ID = SAO.OBJECT_ID
        AND SAO.SCHEMA_ID = SCH.SCHEMA_ID
           
    OPEN Cur_TG
    FETCH Cur_TG INTO @cTgName, @cTableName
   
    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        BEGIN TRY
            -- Para Habilitar
            EXEC ('ENABLE TRIGGER ' + @cTgName + ' ON ' + @cTableName)
            -- Para Deshabilitar
            EXEC ('DISABLE TRIGGER ' + @cTgName + ' ON ' + @cTableName)
        END TRY
        BEGIN CATCH
            PRINT ERROR_MESSAGE()
        END CATCH

        FETCH Cur_TG INTO @cTgName, @cTableName

    END -- Fin del bucle WHILE
   
    CLOSE Cur_TG
    DEALLOCATE Cur_TG

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