欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库表数据统计及数据表的数据大小统计SQL

程序员文章站 2022-05-03 11:52:07
结果: ......
use [yourdbname] -- replace your dbname 
go 
select 
s.name as schemaname, 
t.name as tablename, 
p.rows as rowcounts, 
cast(round((sum(a.used_pages) / 128.00), 2) as numeric(36, 2)) as used_mb, 
cast(round((sum(a.total_pages) - sum(a.used_pages)) / 128.00, 2) as numeric(36, 2)) as unused_mb, 
cast(round((sum(a.total_pages) / 128.00), 2) as numeric(36, 2)) as total_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 
inner join sys.schemas s on t.schema_id = s.schema_id 
group by t.name, s.name, p.rows 
order by s.name, t.name 
go

结果: