SQL Server查询数据库所有表的记录数，占用空间等信息

示例准备：

数据库：TEMP_DATA

切换要执行的其他数据库，分步骤执行如下脚本：

--1.创建存储结果表-----
CREATE TABLE TEMP_DATA.dbo.DB_TableSizes (
TableName VARCHAR(128),
Rows BIGINT,
Reserved VARCHAR(128),
Data VARCHAR(128),
IndexSize VARCHAR(128),
Unused VARCHAR(128)
)

--2.切换要执行的数据库，获取所有用户表的信息-----
DECLARE @TableName NVARCHAR(128)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TEMP_DATA.dbo.DB_TableSizes
EXEC sp_spaceused @TableName

FETCH NEXT FROM table_cursor INTO @TableName
END

CLOSE table_cursor
DEALLOCATE table_cursor

--3.转换大小字段为数字并排序查询-----
SELECT
TableName,
Rows AS 记录数,
CAST( CAST(REPLACE(Reserved, ' KB', '') AS decimal(18, 2))/1024 AS decimal(18, 2)) 保留空间_MB,
CAST(CAST(REPLACE(Data, ' KB', '') AS decimal(18, 2))/1024 AS decimal(18, 2)) 数据空间_MB,
CAST(CAST(REPLACE(IndexSize, ' KB', '') AS decimal(18, 2))/1024 AS decimal(18, 2)) 索引空间_MB,
CAST( CAST(REPLACE(Unused, ' KB', '') AS decimal(18, 2))/1024 AS decimal(18, 2)) 未使用空间_MB
FROM TEMP_DATA.dbo.DB_TableSizes
ORDER BY Rows DESC

--2.清理结果临时表-----
DROP TABLE TEMP_DATA.dbo.DB_TableSizes