27

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. – Shawn Melton Oct 28 '14 at 21:39
  • 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! – WaitForPete Feb 1 '18 at 10:19
33

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
43

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.

  • 3
    Much nicer than the t-sql approach above (which didn't work for me) – Orion Edwards Jul 14 '16 at 4:54
16

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.

2

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.

2

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  
1

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.