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

1.查询当前数据库中所有用户表的数据量（即每个表的记录数）

select  a.name ,  b.rows  from    sysobjects as a

       inner join sysindexes as b on a.id = b.id
where   ( a.type = 'u' )  and ( b.indid in ( 0, 1 ) )
order by b.rows desc

或

select 
    t.name as tablename,
    s.name as schemaname,
    p.rows as rowcounts
from 
    sys.tables t
inner join      
    sys.schemas s on t.schema_id = s.schema_id
inner join      
    sys.partitions p on t.object_id = p.object_id
where 
    p.index_id in (0, 1)  -- 0 = heap table, 1 = clustered index
group by 
    t.name, s.name, p.rows
order by 
    p.rows desc;

说明：
sys.tables：获取数据库中所有用户表。

sys.partitions：每个表（或分区）在物理存储层面的分区信息，包含记录数（rows）。

index_id in (0, 1)：过滤掉非主数据行的分区（如非聚集索引的副本）。

2.在1的基础上增加显示数据库名

select 
    db_name() as databasename,
    t.name as tablename,
    s.name as schemaname,
    sum(p.rows) as rowcounts
from 
    sys.tables t
inner join      
    sys.schemas s on t.schema_id = s.schema_id
inner join      
    sys.partitions p on t.object_id = p.object_id
where 
    p.index_id in (0, 1)
group by 
    t.name, s.name
order by 
    rowcounts desc;

3.跨所有数据库查询每个数据库中每张表的数据量（行数）

需要跨多个数据库查，可以使用 sp_msforeachdb 或手动遍历数据库执行2中语句。

跨所有数据库查询每个数据库中每张表的数据量（行数），使用 sp_msforeachdb 系统存储过程完成：

exec sp_msforeachdb n'
use [?];

if db_id() not in (1, 2, 3, 4)  -- 排除系统数据库（master, tempdb, model, msdb）
begin
    print ''database: [?]'';
    select 
        db_name() as databasename,
        s.name as schemaname,
        t.name as tablename,
        sum(p.rows) as rowcounts
    from 
        sys.tables t
    inner join 
        sys.schemas s on t.schema_id = s.schema_id
    inner join 
        sys.partitions p on t.object_id = p.object_id
    where 
        p.index_id in (0, 1)
    group by 
        s.name, t.name
    order by 
        rowcounts desc;
end
';