Friday, November 11, 2011

Know space distribution by table

We can use sys.dm_db_partition_stats DMV to find out this information
select  OBJECT_NAME(object_id) as objname
        , SUM (reserved_page_count) * 8192/ 1024 asreserved_kb
        , SUM(used_page_count) * 8192 / 1024 as used_kb
from sys.dm_db_partition_stats
group by OBJECT_NAME(object_id)
order by reserved_kb desc
Before SQL Server 2005 We can use the below query to find space used by tables: 
if object_id ('tempdb..#table') is not null
  drop table #table

create table #table (
  name varchar(8000)
, rows int, reserved varchar(50)
, data varchar(50)
, index_size varchar(50)
, unused varchar(50))

insert into #table
exec sp_msforeachtable 'sp_spaceused ''?'''

select name, rows, reserved = REPLACE (reserved, 'KB','')
, data = REPLACE (data, 'KB',''),index_size = REPLACE(index_size, 'KB','')
,unused = REPLACE (unused, 'KB','')
from #table
order by convert ( int , REPLACE (reserved, 'KB','')) desc

drop table #table

No comments:

Post a Comment