---

USE [replTransVas];
SET NOCOUNT ON;

IF CONVERT(sysname, SERVERPROPERTY('ServerName'))
    <> N'WIN-5ODCFIGQRP3\SQL2016SQL'
BEGIN
    ;THROW 50001, N'Неверный экземпляр SQL Server.', 1;
END;

IF @@TRANCOUNT <> 0
BEGIN
    ;THROW 50002, N'Запустите блок вне открытой транзакции.', 1;
END;

DECLARE @obj int = OBJECT_ID(N'dbo.sb_answer', N'U');

IF @obj IS NULL
BEGIN
    ;THROW 50003, N'Таблица dbo.sb_answer не найдена.', 1;
END;

-- Допускаются только два обычных неуникальных
-- некластерных индекса.
IF
(
    SELECT COUNT(*)
    FROM sys.indexes
    WHERE object_id = @obj
      AND name IN
      (
          N'IX_sb_answer_MtsMemberLatest',
          N'IX_sb_answer_MtsParticipantLatest'
      )
      AND type = 2
      AND is_unique = 0
      AND is_primary_key = 0
      AND is_unique_constraint = 0
      AND is_hypothetical = 0
) <> 2
BEGIN
    ;THROW 50004,
        N'Состав индексов отличается от ожидаемого. Изменения отменены.',
        1;
END;

BEGIN TRY
    EXEC sys.sp_executesql N'
        SET LOCK_TIMEOUT 5000;

        IF EXISTS
        (
            SELECT 1
            FROM sys.indexes
            WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
              AND name = N''IX_sb_answer_MtsMemberLatest''
              AND is_disabled = 0
        )
            ALTER INDEX [IX_sb_answer_MtsMemberLatest]
                ON dbo.sb_answer DISABLE;

        IF EXISTS
        (
            SELECT 1
            FROM sys.indexes
            WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
              AND name = N''IX_sb_answer_MtsParticipantLatest''
              AND is_disabled = 0
        )
            ALTER INDEX [IX_sb_answer_MtsParticipantLatest]
                ON dbo.sb_answer DISABLE;
    ';
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message;

    SELECT name, type_desc, is_disabled
    FROM sys.indexes
    WHERE object_id = @obj;

    THROW;
END CATCH;

SELECT
    SYSDATETIME() AS checked_at,
    name,
    type_desc,
    is_disabled
FROM sys.indexes
WHERE object_id = @obj
ORDER BY index_id;