Check Table & Index Space Usage

Posted: August 11, 2010 in SQL Server 2005

Use this script to check disk space used by tables and indexes:

BEGIN

   SET NOCOUNT ON;

   -- Declare variables
   DECLARE @lv_sql VARCHAR(100);

   -- Create temp table
   CREATE TABLE #Test (table_name       VARCHAR(50),
                       row_count        INT,
                       table_size       VARCHAR(50),
                       data_space_used  VARCHAR(50),
                       index_space_used VARCHAR(50),
                       unused_space     varchar(50));

   -- Build SQL string
   SELECT @lv_sql = 'sp_msforeachtable ''sp_spaceused "?"'''
  
   -- Populate temp table
   INSERT INTO #Test EXEC(@lv_sql);

   -- Select records
   SELECT * FROM #Test ORDER BY table_name;

   -- Drop temp table
   DROP TABLE #Test;

END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s