效果如下
记得把数据库名称替换一下
USE 数据库名称; -- 这里修改数据库名称
GO
-- 创建一个临时表来存储空间使用情况
CREATE TABLE #TableSizes (
table_name NVARCHAR(128),
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50)
);
-- 游标遍历所有表,并将空间使用情况插入临时表
DECLARE @table_name NVARCHAR(128);
DECLARE @sql NVARCHAR(4000);
DECLARE table_cursor CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO #TableSizes (table_name, row_count, reserved_size, data_size, index_size, unused_size)
EXEC sp_spaceused ''' + @table_name + ''';';
EXEC sp_executesql @sql;
FETCH NEXT FROM table_cursor INTO @table_name;
END;
CLOSE table_cursor;
DEALLOCATE table_cursor;
-- 查询临时表,并按保留空间排序,找出占用空间最多的表,并使用中文别名
SELECT
table_name AS 表名,
row_count AS 行数,
reserved_size AS 保留空间,
data_size AS 数据空间,
index_size AS 索引空间,
unused_size AS 未使用空间
FROM #TableSizes
ORDER BY CAST(REPLACE(reserved_size, ' KB', '') AS INT) DESC;
-- 删除临时表
DROP TABLE #TableSizes;
GO