SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Vladimir Antovic / Space used by table / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 17:37:22 GMT20RE: Space used by tablehttp://www.sqlservercentral.com/Forums/Topic584473-1400-1.aspxIf 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[code]SELECT so.name as TableName, case when si.indid=255 then 'Image_Text Info' when si.indid &lt;2 then 'Total for Table' else 'Index_Info' end [Data Type], case when si.indid=255 then 'N/A' when si.indid&gt;=2 then 'N/A' when si.indid &lt;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 &gt; 0 order by so.Name[/code]** updated for spelling and to clarify 'Data Total' column as the total usage for the table. ** ToniWed, 19 Nov 2008 09:52:45 GMTtoniupstnyRE: Space used by tablehttp://www.sqlservercentral.com/Forums/Topic584473-1400-1.aspxYou 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_MBfrom 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_idgroup by fg.name, x.[object_name], i.type_desc, x.type_desc, rowsorder by 2, 3Wed, 19 Nov 2008 06:46:16 GMTDennis StephaniSpace used by tablehttp://www.sqlservercentral.com/Forums/Topic584473-1400-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Administration/64602/">Space used by table</A>[/B]Sat, 11 Oct 2008 16:13:30 GMTvladimir.antovic