DROP PROCEDURE IF EXISTS dev.uspIntezmenyEnableObjects
GO

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

  DECLARE 
     @sql nvarchar(max)
    ,@message nvarchar(max)
    ,@tableName nvarchar(150)
    ,@consraintName nvarchar(250)

   -- ================================================================== --
  -- INDEX, TRIGGER, CONSTRAINT bekapcsolás                           --
  -- =======================================================holn======= --
  IF @pIsIndex = 1 BEGIN
    SET @sql = N''  

    SELECT @sql += 'ALTER INDEX ' + i.[name] + ' ON ' + t.[name] + ' REBUILD;' + char(13) + char(10)
    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.schema_id = 1

    SET @message = 'INDEX REBUILD... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
    EXEC sp_executesql @sql
    SET @message = 'INDEX REBUILD DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT  
  END
  
  IF @pIsConstraint = 1 BEGIN
    DECLARE tbl_cur CURSOR LOCAL FOR
    SELECT OBJECT_NAME(parent_object_id) AS table_name, name
    FROM sys.check_constraints 
    WHERE OBJECT_NAME(parent_object_id) LIKE 'T[_]%' 
      AND OBJECT_NAME(parent_object_id) NOT IN(N'T_ENTITYHISTORY', N'T_ENTITYATTRIBUTEHISTORY', 'T_ENTITYATTRIBUTEHISTORY_ARCHIVE')
    UNION ALL
    SELECT OBJECT_NAME(parent_object_id) AS table_name, name
    FROM sys.foreign_keys
    WHERE OBJECT_NAME(parent_object_id) LIKE 'T[_]%' 
      AND OBJECT_NAME(parent_object_id) NOT IN(N'T_ENTITYHISTORY', N'T_ENTITYATTRIBUTEHISTORY', 'T_ENTITYATTRIBUTEHISTORY_ARCHIVE')
    ORDER BY table_name
  
    OPEN tbl_cur
    FETCH NEXT FROM tbl_cur INTO @tableName, @consraintName

    SET @message = 'CONSTRAINT ON... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT

    WHILE @@FETCH_STATUS = 0 BEGIN
      SET @sql = N'ALTER TABLE ' + @tableName + N' WITH CHECK CHECK CONSTRAINT ' + @consraintName + ';'
      BEGIN TRY
        EXEC sp_executesql @sql  
      END TRY
      BEGIN CATCH
        DECLARE @errorMessage nvarchar(4000)
        DECLARE @errorSeverity int 
        DECLARE @errorState int
  
        SELECT   
           @errorMessage = ERROR_MESSAGE()  
          ,@errorSeverity = ERROR_SEVERITY()  
          ,@errorState = ERROR_STATE()

        RAISERROR (@errorMessage, @errorSeverity, @errorState);
        RAISERROR (@sql, 10, 1) WITH NOWAIT      
      END CATCH

      FETCH NEXT FROM tbl_cur
      INTO @tableName, @consraintName
    END

    SET @message = 'CONSTRAINT ON DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT

    CLOSE tbl_cur
    DEALLOCATE tbl_cur
  END  

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