---

USE replTransVas;
SET NOCOUNT ON;

DECLARE @ObjectId int = OBJECT_ID(N'dbo.sb_answer');

IF @ObjectId IS NULL
BEGIN
    RAISERROR(N'Таблица dbo.sb_answer не найдена в replTransVas.', 16, 1);
    RETURN;
END;

-- 1. Индексы и их столбцы.
SELECT
    i.name AS indexName,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_disabled,
    i.has_filter,
    i.filter_definition,
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS columnName
FROM sys.indexes AS i
LEFT JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
LEFT JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE i.object_id = @ObjectId
ORDER BY i.index_id, ic.is_included_column, ic.key_ordinal,
         ic.index_column_id;

-- 2. Размер таблицы — без полного COUNT(*).
SELECT
    SUM(row_count) AS approximateRows,
    CAST(SUM(reserved_page_count) * 8.0 / 1024
         AS decimal(18,2)) AS baseTableReservedMB
FROM sys.dm_db_partition_stats
WHERE object_id = @ObjectId
  AND index_id IN (0, 1);

-- 3. Триггеры: могут добавлять работу к каждому UPDATE.
SELECT
    tr.name AS triggerName,
    tr.is_disabled,
    tr.is_not_for_replication,
    OBJECT_DEFINITION(tr.object_id) AS triggerDefinition
FROM sys.triggers AS tr
WHERE tr.parent_id = @ObjectId;

-- 4. Когда обновлялась статистика.
SELECT
    s.name AS statisticsName,
    s.auto_created,
    s.user_created,
    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 = @ObjectId
ORDER BY s.stats_id;

-- 5. План процедуры репликации из кеша.
SELECT
    ps.last_execution_time,
    ps.execution_count,
    CAST(
        ps.total_worker_time / 1000.0
        / NULLIF(ps.execution_count, 0)
        AS decimal(18,2)
    ) AS averageCpuMs,
    CAST(
        ps.total_elapsed_time / 1000.0
        / NULLIF(ps.execution_count, 0)
        AS decimal(18,2)
    ) AS averageElapsedMs,
    ps.total_logical_reads
        / NULLIF(ps.execution_count, 0) AS averageLogicalReads,
    qp.query_plan
FROM sys.dm_exec_procedure_stats AS ps
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE ps.database_id = DB_ID()
  AND ps.object_id = OBJECT_ID(N'dbo.sp_MSupd_dbosb_answer');

-- 6. Текущая очередь задач на процессоры.
SELECT
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY scheduler_id;