首先要获取数据库下面所有表大小,根据表大小生成500M以下的脚步文件(必须是500M以下)
------------------------------------------------------------------------------------------------------------------------
执行此代码:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB -- 将总空间占用转换为 MB 单位
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.type_desc = 'USER_TABLE' -- 只查询用户表
AND
s.Name = 'dbo' -- 可能需要根据实际情况调整模式名
GROUP BY