Size of all the tables in a database of SQL Server

How to find the size of all the tables residing in a database

Gathering the table wise size data is very important in DBA support life cycle.

Being a DBA, we need to have command on the database growth and table wise growth to get the proper estimates as part of capacity management.

Below query helps us in find the complete details of the tables in particular database.

It will provide the number of rows and size of the table along with index size.

SET NOCOUNT ON
DECLARE            @tablename VARCHAR(250) 
DECLARE            db_cursor CURSOR FOR
SELECT         table_schema+'.'+table_name 
FROM           INFORMATION_SCHEMA.TABLES
WHERE          table_type='BASE TABLE' 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @tablename   
WHILE @@FETCH_STATUS = 0   
BEGIN  
           DECLARE @sqltableusage
           TABLE(name VARCHAR(250),rows INT,reserved VARCHAR(20),
           data VARCHAR(20), index_used VARCHAR(20), unused VARCHAR(20))
           INSERT INTO @sqltableusage
           EXEC sp_spaceused @tablename
      
  FETCH NEXT FROM db_cursor INTO @tablename 
  END
CLOSE db_cursor   
DEALLOCATE db_cursor 

SELECT 
name,rows,REPLACE(reserved,'KB','') Reserved_KB,REPLACE(data,'KB','') Data_KB,REPLACE(index_used,'KB','') Index_Used_KB,REPLACE(unused,'KB','') Unused_KB,CONVERT(DECIMAL(14,2),(REPLACE(reserved,'KB','')))/1024 AS Reserved_MB,CONVERT(DECIMAL(14,2),(REPLACE(data,'KB','')))/1024 AS Data_MB,CONVERT(DECIMAL(14,2),(REPLACE(index_used,'KB','')))/1024 AS Index_Used_MB,CONVERT(DECIMAL(14,2),(REPLACE(unused,'KB','')))/1024 AS Unused_MB
FROM @sqltableusage