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

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    aggGROUP 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.ObjectIDFROM   spaceused spINNER JOIN sys.all_objects so        ON so.[object_id] = sp.ObjectIDWHERE so.is_ms_shipped = 0--AND so.[name] LIKE N''  -- optional name filter--ORDER BY ??

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_idWHERE   t.NAME NOT LIKE 'dt%'    AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255GROUP 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.

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 tablesdeclare @tables table(id int identity(1,1), name varchar(200))declare @tablename varchar(200)insert @tablesselect table_name from information_schema.tables where table_type = 'BASE TABLE'--Define table for resultsdeclare @info table(name varchar(200), rows bigint, reserved varchar(200), data varchar(200), index_size varchar(200), unused varchar(200))--Working varsdeclare @max int, @pos intselect @max = count(1), @pos = 1 from @tables--Loop to get all resultswhile @pos <= @maxbegin    select @tablename = name from @tables where id = @pos    insert @info    exec sp_spaceused @tablename    set @pos = @pos + 1end--return all resultsselect * from @info

I prefer to use variable tables for this, but a cursor/temp table can be used as well.