Thursday, 21 February 2013

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 @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 + + '.' + FROM sys.tables AS T INNER JOIN sys.schemas AS S ON S.schema_id = t.schema_id WHERE [type] ='u'

SET @counter = 1

WHILE @counter <= @max
      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
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

No comments:

Post a Comment