05-SQLServer查看当前库下大于1G的表

1、脚本（分区表显示为多个结果）

;with t1 as 
(
select 
t.name as tableName,

cast(round(((sum(a.total_pages) * 8) / 1024.00 /1024.00),2) as numeric(36,2)) as totalSpaceGB,
p.rows as rowCounts
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
)
select * from t1 where totalSpaceGB >1 order by totalSpaceGB desc

 

2、脚本（分区表显示为1条记录）

;with t1 as 
(
select 
t.name as tableName,

cast(round(((sum(a.total_pages) * 8) / 1024.00 /1024.00),2) as numeric(36,2)) as totalSpaceGB,
p.rows as rowCounts 
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 -- and t.name='HTML_TX_2018'
group by 
t.name,p.rows
)
select * into #table_temp from t1 where totalSpaceGB >1 order by totalSpaceGB desc
select tableName,sum(totalSpaceGB) as GB,sum(rowCounts) as cnt from #table_temp --where tableName='HTML_TX_2018'
group by tableName
order by sum(totalSpaceGB) desc
drop table #table_temp