sp_spaceused - How to measure the size in GB in all the tables in SQL

Question

Following the discussion in 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 the tables of a SQL Server individually using the store procedure sp_spaceused.

Answer 1

The following base query works. It gets the same output as sp_spaceused, using the same algorithm, but much more efficiently. Please do not use the CURSOR + sp_spaceused method; there is absolutely no reason to do that. And a potential problem with using sp_spaceused is that it is intended to be a report proc so the output is all text, not actual numbers, and parsing that back into numbers can be error-prone.

It is also best to not use either sys.tables or sp_msforeachtable as they both exclude indexed views.

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

• Includes XML indexes, FullText indexes, indexed views, etc.
• Breaks down the info for Data vs Index space used

If you need it to work for all databases, it can be easily adapted for that as well.

If you need this data broken down per index, I have adapted the following query in response to this question on DBA.StackExchange: space usage on 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],
       so.[schema_id] AS [SchemaID],
       sp.ObjectID
FROM   spaceused sp
INNER JOIN sys.all_objects so
        ON so.[object_id] = sp.ObjectID
WHERE so.is_ms_shipped = 0
--AND so.[name] LIKE N''  -- optional name filter
--ORDER BY ??

Answer 2

I know this isn't exactly what you are asking for as it DOESN'T use sp_spaceused but this will provide the results 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 it to use sp_spaceused.

Answer 3

There are a couple of options here:

sp_msforeachtable

sp_msforeachtable 'exec sp_spaceused [?]'

While sp_msforeachtable can be used for this is has a couple of limitations:

• Firstly you end up with a result set for each table that is run, which is hard to work with
• If you run it over too many tables then you hit the limit of result sets that SSMS will support (think this is usually around the 200 mark)

Collated results

With a little more work, we can collate all the results into a single data set, avoiding these limitations. The pattern of this solution is similar whenever there is a requirement to run a sproc a bunch of times with different parameters and to collate the results from those 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 a cursor/temp table can be used as well.