SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Space used by table


Space used by table

Author
Message
vladimir.antovic
vladimir.antovic
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 49
Comments posted to this topic are about the item Space used by table
Dennis Stephani
Dennis Stephani
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 90
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
toniupstny
toniupstny
SSC Eights!
SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)

Group: General Forum Members
Points: 992 Visits: 940
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search