How to determine the size of my tables in the SQL Server database

  • Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?

    This answer on SO has a script that is pretty simple and does not require undocumented procedure.

    The answer given by AA.SC is very useful except that the msdb database identifier needs to be removed from all the DMVs in the join. As it stands it only reports on tables in msdb itself!

  • For single table you can use

    sp_spaceused MyTable

    For all tables in a database you can use it with sp_msforeachtable as follwoing

    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,
    COUNT(*) AS col_count,
    FROM #temp a
    INNER JOIN information_schema.columns b
    ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY a.table_name, a.row_count, a.data_size
    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
    DROP TABLE #temp
  • If you don't want to write a script, you can also open the much underused 'Object Explorer Details' in SSMS (Shortcut key F7).

    Top Level of Object Explorer Details

    From the Top-Level, open the Tables folder to get a list of all the tables in your database.

    You may need to customise the columns to see the Space Used. This can be done by right clicking on the header row and choosing the columns you wish to display.

    Table data size in SSMS

    There's plenty more data like this available in Object Explorer Details.

    Much nicer than the t-sql approach above (which didn't work for me)

  • In SSMS right click on Database, select Reports, Standard Reports, Disk Usage by Top Tables.

    The report will give you number of rows and kilobytes used per table.

  • Take a look at sys.dm_db_partition_stats (

    There are some sample queries at that link and also at

    You can modify to suit your needs i.e. filter in/out non-clustered indexes. Multiply page count by 8 to get size in KB, then divide by 2^10 (=1024) to convert to MB if required.

    sp_spaceused ( will also give table size information.

  • To get table size information I like to use following script

    SELECT + '.' + t.NAME AS TableName,  
            ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server  
            ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,  
            ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  
    FROM    msdb.sys.tables t  
            INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id  
            INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id  
            INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID  
                                                AND i.index_id = p.index_id  
            INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id  
    WHERE   t.type_desc = 'USER_TABLE'  
            AND i.index_id <= 1  --- Heap\ CLUSTERED
            AND t.NAME='MYTableName' -- Replace with valid table name
    GROUP BY + '.' + t.NAME,  
            i.[object_id],i.index_id,, p.[Rows]  
    ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC  
  • Go to to database then right click and click on reports then standard reports then disk usage by table. This will give you all the ta les in that database and thier records, data, indexes etc

  • you can use the following script that calculates the volume for each table and another result set of the total per data base

        IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL DROP TABLE #SpaceUsed
        CREATE TABLE #SpaceUsed 
        TableName sysname ,
        [Rows] int ,
        [Reserved] varchar(20),
        [Data] varchar(20),
        [Index_Size] varchar(20),
        [Unused] varchar(20),
        [Reserved_KB] bigint,
        [Data_KB] bigint,
        [Index_Size_KB] bigint,
        [Unused_KB] bigint
        SELECT @CMD +='EXEC sp_spaceused ' +  ''''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+';'+CHAR(10)
        --PRINT @CMD
         INSERT INTO #SpaceUsed (TableName ,[Rows] , [Reserved], [Data] , [Index_Size] , [Unused] )
         EXEC sp_executesql @CMD
         UPDATE #SpaceUsed 
         SET [Reserved_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Reserved] , ' KB', '')))),
             [Data_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Data] , ' KB', '')))),
             [Index_Size_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Index_Size] , ' KB', '')))),
             [Unused_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Unused] , ' KB', ''))))
         SELECT TableName, [Rows], Reserved_KB , Data_KB , Index_Size_KB , Unused_KB ,  Data_KB / 1024.0 Data_MB , Data_KB / 1024.0 / 1024.0 Data_GB
         FROM #SpaceUsed
         ORDER BY Data_KB DESC 
         SELECT SUM(Reserved_KB) Reserved_KB , SUM(Data_KB) Data_KB, SUM(Index_Size_KB) Index_Size_KB , SUM(Unused_KB) Unused_KB ,SUM(Data_KB / 1024.0) Data_MB , SUM(Data_KB / 1024.0 / 1024.0) Data_GB
         FROM #SpaceUsed
         DROP TABLE #SpaceUsed

    damn, this script is really good, thanks... I tested it on Azure SQL and works...

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM

Tags used