SQL Server查询所有表数据量的代码实例

方法1：使用sys.tables系统视图（推荐）

-- 获取所有表的行数和存储空间信息
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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.is_ms_shipped = 0
    AND i.object_id > 255
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    p.rows DESC;

方法2：使用sp_spaceused存储过程

-- 创建临时表存储结果
CREATE TABLE #TableSizes
(
    TableName NVARCHAR(128),
    Rows BIGINT,
    ReservedSpace VARCHAR(50),
    DataSpace VARCHAR(50),
    IndexSize VARCHAR(50),
    UnusedSpace VARCHAR(50)
);

-- 使用游标遍历所有表
DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

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 @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'EXEC sp_spaceused ''' + @TableName + '''';

    INSERT INTO #TableSizes (TableName, Rows, ReservedSpace, DataSpace, IndexSize, UnusedSpace)
    EXEC(@SQL);

    FETCH NEXT FROM table_cursor INTO @TableName;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- 显示结果
SELECT * FROM #TableSizes ORDER BY Rows DESC;

-- 清理
DROP TABLE #TableSizes;

方法3：使用INFORMATION_SCHEMA和sys.partitions（简单快速）

-- 简单查询所有表的行数
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.NAME AS TableName,
    SUM(p.rows) AS RowCounts
FROM 
    sys.tables t
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE 
    p.index_id IN (0, 1) -- 堆表或聚集索引
    AND t.is_ms_shipped = 0 -- 排除系统表
GROUP BY 
    t.schema_id, t.Name
ORDER BY 
    RowCounts DESC;

方法4：查询特定数据库的所有表数据量

-- 指定数据库名称
USE YourDatabaseName;
GO

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS TotalRows,
    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.is_ms_shipped = 0
GROUP BY 
    t.schema_id, t.name
ORDER BY 
    TotalRows DESC;

方法5：显示详细的空间使用情况

-- 显示详细的表空间使用情况
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB
FROM 
    sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    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.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC;

方法6：快速统计总行数（最简版本）

-- 最快的方式获取表行数
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    SUM(row_count) AS TotalRows
FROM 
    sys.dm_db_partition_stats
WHERE 
    index_id IN (0, 1)  -- 堆表或聚集索引
    AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
GROUP BY 
    object_id
ORDER BY 
    TotalRows DESC;