Question

sql系统里如何查询一个集群各个表当前数据量大小从高到低排名（以MB为单位），效果如下图，请写出sql语句

Answer 1

create table tmp
(
name varchar(50),
rows int,
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)  
insert into tmp (name,rows,reserved,  data,index_size,unused)
exec sp_msforeachTable  @Command1="sp_spaceused '?'"

select name as table_name,cast(substring(data,patindex('%[^0-9][0-9]%',data)+1,patindex('%[0-9][^0-9]%',data)-patindex('%[^0-9][0-9]%',data)) as float)/1024 as MB
, rows as table_rows from tmp  order by cast(SUBSTRING(data,1,LEN(data)-2) AS int) desc
 
drop table tmp
 
 ------------第一列不知道什么意思 可以帮你把后三列查出来，结果如下