SQL Server - Table Size

It is important for DBA to have command on the database growth and table wise data growth to get the proper estimate as part of capacity management.

I use the below given query to get the table size of a database.

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

Output Result