Script to get table size statistics - SQL Server by serverku

Recently we have seen the post for database size information and the script used for the same. Hope you liked that post. Sometime we need to have details like database size for growth and disk planning we viewed in an earlier post, the same way I am repeating this post in details like earlier size statistics for database and now it is for tables. You have seen in an earlier post we used sp_spaceused to get database size information, but what about if we pass the table name as parameter, I will return for particular table size statistics in one user database,

USE [AdventureWorksLT2008]
GO
EXEC sp_spaceused '[SalesLT].[Product]'; 

/* Output :

name     rows  reserved  data    index_size  unused
-------- ----- --------- ------- ----------- ------
Product  295   928 KB    808 KB  96 KB       24 KB

*/

The same information can be achieved for all tables using a loop (while loop or cursor ) or sp_msforeachtable system procedure,

USE [AdventureWorksLT2008]
GO
CREATE TABLE #tablesizestatistics 
  ( 
     Tablename  VARCHAR(100), 
     [rows]     VARCHAR(100), 
     reserved   VARCHAR(50), 
     data       VARCHAR(50), 
     index_size VARCHAR(50), 
     unused     VARCHAR(50) 
  ) 

EXEC Sp_msforeachtable 
  @command1='INSERT INTO #TableSizeStatistics exec sp_spaceused ''?''' 

SELECT * 
FROM   #tablesizestatistics 

/* Output :

Tablename                        rows   reserved  data    index_size  unused
-------------------------------- ------ --------- ------- ----------- -------
BuildVersion                     1      16 KB     8 KB    8 KB        0 KB
Address                          450    336 KB    72 KB   160 KB      104 KB
Customer                         847    512 KB    272 KB  136 KB      104 KB
CustomerAddress                  417    80 KB     32 KB   48 KB       0 KB
Product                          295    928 KB    808 KB  96 KB       24 KB
ProductCategory                  41     48 KB     8 KB    40 KB       0 KB
ProductDescription               762    248 KB    144 KB  56 KB       48 KB
ProductModel                     128    248 KB    56 KB   104 KB      88 KB
ProductModelProductDescription   762    112 KB    48 KB   64 KB       0 KB
SalesOrderDetail                 542    120 KB    40 KB   80 KB       0 KB
SalesOrderHeader                 32     64 KB     8 KB    56 KB       0 KB
ErrorLog                         0      0 KB      0 KB    0 KB        0 KB

*/

You may use a loop instead of above method and run sp_spaceused inside it to get for each table. But without a loop and sp_msforeachtable and sp_spaceused we have another script which help us to get each tables size statistic,

USE [AdventureWorksLT2008]
GO
SELECT Schema_name(t.schema_id) + '.' + t.name        AS TableName, 
       p.rows                                         AS Rows, 
       Sum(a.total_pages) * 8                         AS TotalSpace_KB, 
       Sum(a.used_pages) * 8                          AS UsedSpace_KB, 
       (Sum(a.total_pages) - Sum(a.used_pages)) * 8 AS UnusedSpace_KB 
FROM   sys.tables t 
       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 
GROUP  BY Schema_name(t.schema_id) + '.' + t.name, 
          p.rows 
ORDER  BY totalspace_kb DESC

/* Output :

TableName                                Rows       TotalSpace_KB  UsedSpace_KB  UnusedSpace_KB
---------------------------------------- ---------- -------------- ------------- --------------
SalesLT.Product                          295        928            904           24
SalesLT.Customer                         847        512            408           104
SalesLT.Address                          450        336            232           104
SalesLT.ProductDescription               762        248            200           48
SalesLT.SalesOrderDetail                 542        120            120           0
SalesLT.ProductModelProductDescription   762        112            112           0
SalesLT.ProductModel                     128        112            104           8
SalesLT.CustomerAddress                  417        80             80            0
SalesLT.SalesOrderHeader                 32         64             64            0
SalesLT.ProductCategory                  41         48             48            0
dbo.BuildVersion                         1          16             16            0
dbo.ErrorLog                             0          0              0             0

*/

Hope you enjoyed this post and this may help you to get same table statistics. I would like you to share if we can have some other scripts or some additional information for tables.