October 11, 2008 at 4:13 pm
Comments posted to this topic are about the item Space used by table
November 19, 2008 at 6:46 am
You might also try this script (if SQL 2005+). It works directly on the SYS tables. You can collapse things down to group on only the object_name and rows if you want. It will show you all tables in a database at once.
select
fg.name filegroup_name,
[object_name],
i.type_desc index_type,
x.type_desc alloc_type,
rows,
sum(total_pages) total_pages,
sum(total_pages) * 8192 / 1024 / 1024 total_MB,
sum(data_pages) data_pages,
sum(data_pages) * 8192 / 1024 / 1024 data_MB,
sum(used_pages) used_pages,
sum(used_pages) * 8192 / 1024 / 1024 used_MB
from sys.filegroups fg
inner join
( select
au.data_space_id,
p.object_id,
object_name(p.object_id) [object_name],
p.index_id,
p.rows,
au.type_desc,
au.total_pages,
au.data_pages,
au.used_pages
from sys.partitions p
inner join sys.allocation_units au on p.hobt_id = au.container_id
where au.type in(1,3)
union all
select
au.data_space_id,
p.object_id,
object_name(p.object_id) [object_name],
p.index_id,
p.rows,
au.type_desc,
au.total_pages,
au.data_pages,
au.used_pages
from sys.partitions p
inner join sys.allocation_units au on p.partition_id = au.container_id
where au.type = 2
) x on x.data_space_id = fg.data_space_id
inner join sys.indexes i on x.object_id = i.object_id and x.index_id = i.index_id
group by fg.name, x.[object_name], i.type_desc, x.type_desc, rows
order by 2, 3
November 19, 2008 at 9:52 am
If you are still running some SQL 2K databases, then the following will get you the space usage results from the system tables (no need for cursors or temp tables).
Note that for a clustered index, the sp_spaceused query will show the intermediate 'node' level pages as a separate index size while the data and 'leaf level' of the index are counted as one and the same.
So for a table of 50 pages allocated having a clustered index you might see it split as 40 pages of data and 10 for index (really only the intermediate level of the clustered index) when you do the sp_spaceused.
You might also consider doing a "DBCC UPDATEUSAGE (0) " before running your query to get the latest data (though SQL should keep this relatively current). Alternatively with a sp_spaceused you can use the optional '@updateusage=true' parameter to the same ends.
Toni
SELECT so.name as TableName,
case when si.indid=255 then 'Image_Text Info'
when si.indid <2 then 'Total for Table'
else 'Index_Info' end [Data Type],
case when si.indid=255 then 'N/A'
when si.indid>=2 then 'N/A'
when si.indid <2 then cast(si.rowcnt as varchar(20)) end [Rows],
si.reserved * 8 [Allocated_KB],
si.used*8 [Used_kb],
(si.reserved-si.used)*8 [Unused_KB]
FROM sysobjects so
JOIN sysindexes si
ON si.id = so.id
WHERE so.Type='U' AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0 and si.reserved > 0
order by so.Name
** updated for spelling and to clarify 'Data Total' column as the total usage for the table. ** Toni
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply