从监控存储代码变化到监控表变化，不用触发器

简书链接:从监控存储代码变化到监控表变化，不用触发器
文章字数：147,阅读全文大约需要1分钟
触发器的实现原理是时时刻刻监听这种影响性能，而拉取到本地的原理是记录每次拉取时候的内容。

对于表，可以记录表的大小从而实现。 新增用count(1)

EXEC sp_spaceused ziptest;
SELECT 
    t.NAME AS TableName,
    SUM(p.rows) AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY 
    t.NAME
HAVING 
    t.NAME = 'ziptest';


SELECT  TOP 1 
    OBJECT_NAME(object_id) AS TableName,
    SUM(CASE 
            WHEN index_id < 2 THEN (page_count * 8) 
            ELSE 0 
        END) AS DataSizeKB,
    SUM(CASE 
            WHEN index_id >= 2 THEN (page_count * 8) 
            ELSE 0 
        END) AS IndexSizeKB
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE 
    OBJECT_NAME(object_id) = 'ziptest'
GROUP BY 
    object_id;

最后整到了这个里面，对于表的变化也一目两然