Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Space used by table Expand / Collapse
Author
Message
Posted Saturday, October 11, 2008 4:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 03, 2012 2:37 AM
Points: 14, Visits: 49
Comments posted to this topic are about the item Space used by table
Post #584473
Posted Wednesday, November 19, 2008 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 6:43 AM
Points: 12, Visits: 88
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
Post #605095
Posted Wednesday, November 19, 2008 9:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:23 AM
Points: 242, Visits: 939
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
Post #605258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse