【脚本即时用】sqlserver查询库及表大小

查询库的大小

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS Size
FROM    sys.master_files
GROUP BY     database_id;

SQLserver 查看表大小（每个表的名称、行数、总空间、已使用空间和未使用空间）

SELECT 
    t.NAME AS TableName,
    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
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY t.Name, p.Rows
ORDER BY   p.Rows desc