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))
    SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,
    a.row_count,
    COUNT(*) AS col_count,
    a.data_size
    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 (http://msdn.microsoft.com/en-us/library/ms187737.aspx).

    There are some sample queries at that link and also at http://blogs.msdn.com/b/cindygross/archive/2010/04/02/dmv-series-sys-dm-db-partition-stats.aspx

    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 (http://msdn.microsoft.com/en-us/library/ms188776.aspx) will also give table size information.

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

    SELECT  sc.name + '.' + t.NAME AS TableName,  
            p.[Rows],  
            ( 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 sc.name + '.' + t.NAME,  
            i.[object_id],i.index_id, i.name, 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

        SET NOCOUNT ON 
        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
        )
    
        DECLARE @CMD NVARCHAR(MAX) =''
        SELECT @CMD +='EXEC sp_spaceused ' +  ''''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+';'+CHAR(10)
        FROM INFORMATION_SCHEMA.TABLES 
        --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