---

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;

IF OBJECT_ID(N'dbo.sb_answer', N'U') IS NULL
   OR OBJECT_ID(N'dbo.sp_MSupd_dbosb_answer', N'P') IS NULL
BEGIN
    ;THROW 50003, N'Не найдена таблица или процедура репликации.', 1;
END;

-- 1. Настройки автоматического обновления статистики.
SELECT
    N'01_DATABASE_SETTINGS' AS section,
    name,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE database_id = DB_ID();

-- 2. Статистика до изменения.
SELECT
    N'02_STATS_BEFORE' AS section,
    s.name,
    s.no_recompute,
    p.last_updated,
    p.rows,
    p.rows_sampled,
    p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(
    s.object_id, s.stats_id
) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.sb_answer');

-- 3. Все операции с ограниченным ожиданием блокировки
-- выполняются внутри одного динамического блока.
-- После выхода исходный LOCK_TIMEOUT восстановится автоматически.
EXEC sys.sp_executesql N'
    SET LOCK_TIMEOUT 5000;

    UPDATE STATISTICS dbo.sb_answer;

    EXEC sys.sp_recompile
        @objname = N''dbo.sp_MSupd_dbosb_answer'';
';

SELECT
    N'03_ACTION' AS section,
    N'STATISTICS_UPDATED_AND_RECOMPILE_REQUESTED' AS result,
    SYSDATETIME() AS completed_at;

-- 4. Статистика после изменения.
SELECT
    N'04_STATS_AFTER' AS section,
    s.name,
    s.no_recompute,
    p.last_updated,
    p.rows,
    p.rows_sampled,
    p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(
    s.object_id, s.stats_id
) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.sb_answer');

-- 5. Ищем источник отметки toDEl в основной базе.
-- Показываем текст модулей, чтобы проверить границы UPDATE.
SELECT
    N'05_SOURCE_MODULES' AS section,
    N'myNewVasya' AS database_name,
    sc.name AS schema_name,
    o.name AS object_name,
    o.type_desc,
    m.definition
FROM myNewVasya.sys.sql_modules AS m
JOIN myNewVasya.sys.objects AS o
  ON o.object_id = m.object_id
JOIN myNewVasya.sys.schemas AS sc
  ON sc.schema_id = o.schema_id
WHERE m.definition COLLATE Cyrillic_General_CI_AS
      LIKE N'%toDEl%'
ORDER BY sc.name, o.name;

-- 6. Та же проверка в базе подписчика.
SELECT
    N'06_SUBSCRIBER_MODULES' AS section,
    DB_NAME() AS database_name,
    sc.name AS schema_name,
    o.name AS object_name,
    o.type_desc,
    m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o
  ON o.object_id = m.object_id
JOIN sys.schemas AS sc
  ON sc.schema_id = o.schema_id
WHERE m.definition COLLATE Cyrillic_General_CI_AS
      LIKE N'%toDEl%'
ORDER BY sc.name, o.name;

-- 7. Текущее состояние очереди.
EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds
    @publisher         = N'WIN-5ODCFIGQRP3\SQL2016SQL',
    @publisher_db      = N'myNewVasya',
    @publication       = N'V3Trans',
    @subscriber        = N'WIN-5ODCFIGQRP3\SQL2016SQL',
    @subscriber_db     = N'replTransVas',
    @subscription_type = 0;