Sp_spaceused - How to measure size in GB in all SQL tables

Question

After discussing How to measure table size in GB in a table in SQL , I'm looking for a solution to measure the space used by all SQL Server tables using the sp_spaceused storage sp_spaceused .

Answer 1

The following basic request is executed. It gets the same result as sp_spaceused , using the same algorithm, but much more efficiently. Do not use the CURSOR + sp_spaceused ; there is absolutely no reason for this. And the potential problem with using sp_spaceused is that it is intended for reporting, so the output is all text, not actual numbers, and parsing that returns to numbers may be error prone.

It is also better not to use either sys.tables or sp_msforeachtable , since both of them exclude indexed views.

The following is exactly the same as sp_spaceused in terms of:

• Includes XML indexes, FullText indexes, indexed views, etc.
• Interrupts information about used data space and index

If you need it to work for all databases, it can also be easily adapted for this.

If you need this data, divided into one index, I applied the following query in response to this question in DBA.StackExchange: using space in sys.allocation_units and sp_spaceused

 ;WITH extra AS ( -- Get info for FullText indexes, XML Indexes, etc SELECT sit.[object_id], sit.[parent_id], ps.[index_id], SUM(ps.reserved_page_count) AS [reserved_page_count], SUM(ps.used_page_count) AS [used_page_count] FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables sit ON sit.[object_id] = ps.[object_id] WHERE sit.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236) GROUP BY sit.[object_id], sit.[parent_id], ps.[index_id] ), agg AS ( -- Get info for Tables, Indexed Views, etc (including "extra") SELECT ps.[object_id] AS [ObjectID], ps.index_id AS [IndexID], SUM(ps.in_row_data_page_count) AS [InRowDataPageCount], SUM(ps.used_page_count) AS [UsedPageCount], SUM(ps.reserved_page_count) AS [ReservedPageCount], SUM(ps.row_count) AS [RowCount], SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count) AS [LobAndRowOverflowUsedPageCount] FROM sys.dm_db_partition_stats ps GROUP BY ps.[object_id], ps.[index_id] UNION ALL SELECT ex.[parent_id] AS [ObjectID], ex.[object_id] AS [IndexID], 0 AS [InRowDataPageCount], SUM(ex.used_page_count) AS [UsedPageCount], SUM(ex.reserved_page_count) AS [ReservedPageCount], 0 AS [RowCount], 0 AS [LobAndRowOverflowUsedPageCount] FROM extra ex GROUP BY ex.[parent_id], ex.[object_id] ), spaceused AS ( SELECT agg.[ObjectID], OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName], OBJECT_NAME(agg.[ObjectID]) AS [TableName], SUM(CASE WHEN (agg.IndexID < 2) THEN agg.[RowCount] ELSE 0 END) AS [Rows], SUM(agg.ReservedPageCount) * 8 AS [ReservedKB], SUM(agg.LobAndRowOverflowUsedPageCount + CASE WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount) ELSE 0 END) * 8 AS [DataKB], SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount - CASE WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount ELSE 0 END) * 8 AS [IndexKB], SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB], SUM(agg.UsedPageCount) * 8 AS [UsedKB] FROM agg GROUP BY agg.[ObjectID], OBJECT_SCHEMA_NAME(agg.[ObjectID]), OBJECT_NAME(agg.[ObjectID]) ) SELECT sp.SchemaName, sp.TableName, sp.[Rows], sp.ReservedKB, (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB], sp.DataKB, (sp.DataKB / 1024.0 / 1024.0) AS [DataGB], sp.IndexKB, (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB], sp.UsedKB AS [UsedKB], (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB], sp.UnusedKB, (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB], so.[type_desc] AS [ObjectType] FROM spaceused sp INNER JOIN sys.objects so ON so.[object_id] = sp.ObjectID WHERE so.is_ms_shipped = 0 --so.[name] LIKE N'' -- optional name filter 

Answer 2

I know that this is not quite what you are asking for, since it does not use sp_spaceused, but it will give the results that you are after.

 SELECT s.Name AS SchemaName, t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, ( SUM(a.total_pages) * 8 ) / 1024.0 AS TotalSpaceMB, (( SUM(a.total_pages) * 8 ) / 1024.0)/1024.0 AS TotalSpaceGB, SUM(a.used_pages) * 8 AS UsedSpaceKB, ( SUM(a.used_pages) * 8 ) / 1024.0 AS UsedSpaceMB, (( SUM(a.used_pages) * 8 ) / 1024.0) /1024.0 AS UsedSpaceGB, ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB, ( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0 AS UnusedSpaceMB, (( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0)/1024.0 AS UnusedSpaceGB, GROUPING(t.Name) FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY s.Name, t.Name, p.Rows WITH ROLLUP ORDER BY s.Name, t.Name 

Let me know if you really need to use sp_spaceused.

Answer 3

There are several options here:

sp_msforeachtable

 sp_msforeachtable 'exec sp_spaceused [?]' 

While sp_msforeachtable can be used for this, it has several limitations:

• Firstly, you get the result for each table that is running, which is difficult to work with
• If you run too many tables, you fall within the scope of the result sets that SSMS will support (I think this is usually around 200).

Compiled Results

With a little work, we can collect all the results in one data set, avoiding these limitations. The structure of this solution is similar whenever you need to run sproc several times with different parameters and compare the results of these runs.

 --Get list of interesting tables declare @tables table(id int identity(1,1), name varchar(200)) declare @tablename varchar(200) insert @tables select table_name from information_schema.tables where table_type = 'BASE TABLE' --Define table for results declare @info table(name varchar(200), rows bigint, reserved varchar(200), data varchar(200), index_size varchar(200), unused varchar(200)) --Working vars declare @max int, @pos int select @max = count(1), @pos = 1 from @tables --Loop to get all results while @pos <= @max begin select @tablename = name from @tables where id = @pos insert @info exec sp_spaceused @tablename set @pos = @pos + 1 end --return all results select * from @info 

I prefer to use variable tables for this, but you can also use a cursor / tempo table.

Answer 4

I assume you are trying to use sp_spaceused to find the size for ALL tables in db?

If so, you can do this:

 USE database_name GO sp_msforeachtable 'exec sp_spaceused [?]'