DROP PROCEDURE IF EXISTS dev.uspIntezmenyDisableObjects
GO

CREATE PROCEDURE dev.uspIntezmenyDisableObjects
   @pIsIndex bit = 1
  ,@pIsConstraint bit = 1
  ,@pIsTrigger bit = 1
AS 
BEGIN

  DECLARE 
     @sql nvarchar(max)
    ,@message nvarchar(max)
   -- ================================================================== --
  -- INDEX, TRIGGER, CONSTRAINT kikapcsolás                              --
  -- =================================================================== --

  IF @pIsIndex = 1 BEGIN

    SET @sql = N''  

    SELECT @sql += N'
      ALTER INDEX '  + i.name + ' ON ' + t.name + N' DISABLE;'
    FROM sys.indexes i
      INNER JOIN sys.tables t ON t.object_id = i.object_id
    WHERE i.type = 2 /*non clustered*/
      AND t.name  LIKE 'T[_]%' AND t.name <> 'T_ENTITYHISTORY'
      AND t.schema_id = 1
    
    SET @message = 'INDEX DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
    EXEC sp_executesql @sql
    SET @message = 'INDEX DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
  END

  IF @pIsConstraint = 1 BEGIN
    SET @sql = N''

    SELECT @sql = @sql + N'
      ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(fk.object_id) + CHAR(13) + CHAR(10)
    FROM sys.foreign_keys fk 
    WHERE OBJECT_NAME(fk.parent_object_id) LIKE 'T[_]%'
    
    SET @message = 'CONSTRAINT OFF... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
    EXEC sp_executesql @sql
    SET @message = 'CONSTRAINT OFF DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT 
  END

  IF @pIsTrigger = 1 BEGIN
    SET @message = 'TRIGGER DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
    EXEC sp_msforeachtable 'DISABLE TRIGGER ALL ON ?'
    SET @message = 'TRIGGER DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT
  END
END 
GO