Display data and index space usege for each table in MS SQL

I have came across a requirement to generate regular report to for the data and index space usage for each table as well as for database. So I have wirttern a small script to do it using Sp_spaceused stored procedure. My script is posted below which might be useful to you. If you find useful please comment in comment section. And feel free to post about correction or if you find better script for this. Data and index size in result set is in MB, it can be changed to Bytes/KB/GB using changes in SELECT query at the end. Below script is self descriptive (at least I think so!!!) If you need any help please leave comment.

DECLARE @counter INT

DECLARE @max INT

DECLARE @name VARCHAR(100)

DECLARE @t TABLE (id INT IDENTITY, name VARCHAR(100))

DECLARE @t1 TABLE (name VARCHAR(100), [rows] INT, reserved VARCHAR(100), data VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))

DECLARE @t2 TABLE (name VARCHAR(100), [rows] BIGINT, reserved BIGINT, data BIGINT, index_size BIGINT, unused BIGINT)

INSERT INTO @t (name)

SELECT s.name + + '.' + t.name FROM sys.tables AS T INNER JOIN sys.schemas AS S ON S.schema_id = t.schema_id WHERE [type] ='u'

SET @max = @@ROWCOUNT

SET @counter = 1

WHILE @counter <= @max

      BEGIN

      SELECT @name = name FROM @t WHERE id = @counter

      INSERT INTO @t1(name, [rows], reserved, data, index_size, unused)

      EXEC Sp_spaceused @name

      SET @counter = @counter + 1
      INSERT INTO @t2(name, [rows], reserved, data, index_size, unused)
      SELECT @name
            , CAST([rows] AS BIGINT)
            , CAST(REPLACE(reserved, ' KB', '') AS BIGINT)*1024
            , CAST(REPLACE(data, ' KB', '') AS BIGINT)*1024
            , CAST(REPLACE(index_size, ' KB', '') AS BIGINT)*1024
            , CAST(REPLACE(unused , ' KB', '') AS BIGINT)*1024
      FROM @t1


      DELETE FROM @t1

      END 

           

SELECT SUM(reserved)/1024/1024 reserved

      , SUM(data)/1024/1024 data

      , SUM(index_size)/1024/1024 index_size

      , SUM(unused)/1024/1024 unused  FROM @t2

     

SELECT name AS table_name, data_size, index_size, (data_size + index_size) AS total_size

FROM (     

SELECT name, data/1024/1024 AS data_size, index_size/1024/1024 AS index_size FROM @t2

) AS A ORDER BY total_size DESC

MS SQL 2008 | MS SQL 2008 R2 | MS SQL 2012 | T-SQL | T-SQL Tutorials | Learn T-SQL | | Microfot SQL Server | How to calculate data space | How to calculate index space | How to calculate database size