SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Simple Script to Report Tables Sizes in a SQL2K Database

Last week we had an issue with a database growing unexpectedly. The database in question supported an infrastructure type 3rd party application and the administrators over said application were scratching their head as to why it could have grown. I threw together a quick script using sp_spaceused to figure out what the table sizes were in the database. However, I ended up using a temporary table to dump all the information from sp_spaceused for each table and then had to parse out the KB. That solution didn't satisfy me very much. I've tinkered with the script and threw together a simple stored procedure that does the same thing, only it has options so I can get the numbers back in byes, KB, MB, or GB as necessary.

This script only works on SQL Server 2000, not SQL Server 2005. Let me rephrase that. It'll work, but it may not be accurate because of some changes in physical structure in the new version. The script is designed to be placed in the master database though if you have a work database, it could work there, too. If in a work database, change it so the stored procedure doesn't begin with sp_ as SQL Server will look in the master database first, even if the three part naming convention is used.

USE master

IF EXISTS (SELECT id FROM sysobjects WHERE name = 'sp_ReportTableSizes' AND xtype = 'P')
  DROP PROC sp_ReportTableSizes

CREATE PROC sp_ReportTableSizes
  @ConversionType char(1) = 'K'
  DECLARE @conversion float

  IF @ConversionType = 'B' SET @conversion = 1.0
    ELSE IF @ConversionType = 'M' SET @conversion = (1024.0 * 1024.0)
      ELSE IF @ConversionType = 'G' SET @conversion = (1024.0 * 1024.0 * 1024.0)
        ELSE SET @conversion = 1024.0

  SELECT su.tablename, (su.tablesize * spt.low / @conversion) totalsize
  FROM master..spt_values spt,
    (SELECT so.name tablename, SUM(si.reserved) tablesize
     FROM sysobjects so
       JOIN sysindexes si
         ON so.id = si.id
     WHERE si.indid IN (0, 1, 255)
       AND so.xtype = 'U'
     GROUP BY so.name) su
  WHERE spt.number = 1
    AND spt.type = 'E'
  ORDER BY su.tablesize DESC, su.tablename ASC

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.