Script to Find Disk Space Occupied by a Table

Before SQL Server 2005 Enterprise Manager made it easy, I used this script to figure out how much disk space a table took up, and the "real" (average, at least) width of a row.

-- Lists the tables in the selected database and the disk space they use. 
-- Also displays the average 'width' of each row in each table. 
SET NOCOUNT ON
DECLARE @sSourceDB AS sysname
SET @sSourceDB = 'Northwind'    -- <====== Set this value.

-- Holds the space used for each table. Column names reflect sp_spaceused().
CREATE TABLE #SpaceUsed 
(
   name       VARCHAR(128),
   rows       VARCHAR(11),
   reserved   VARCHAR(18),
   data       VARCHAR(18),
   index_size VARCHAR(18),
   unused     VARCHAR(18)
)

-- Create and open a cursor on the tables.
DECLARE curTables CURSOR 
    FOR 
 SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_CATALOG = @sSourceDB
    AND TABLE_TYPE = 'BASE TABLE'

OPEN curTables 

-- Iterate the cursor, populating #SpaceUsed for each table from sp_spaceused().
DECLARE @sTableName sysname

FETCH NEXT
   FROM curTables
   INTO @sTableName 

WHILE 0 = @@FETCH_STATUS
BEGIN
   DECLARE @sSql SYSNAME
   SET @sSql = 'EXEC ' + @sSourceDB +
               '..sp_executesql N''INSERT #SpaceUsed EXEC sp_spaceused ' +
               @sTableName + '''' 
   PRINT @sSql
   EXEC(@sSql)

   FETCH NEXT
      FROM curTables
      INTO @sTableName
END

CLOSE curTables
DEALLOCATE curTables 

-- Display results.
SELECT
   name       AS 'Table Name',
   rows       AS 'Row Count',

   CASE CAST(REPLACE(rows, ' KB', '') AS INT)
       WHEN 0  THEN 'N/A'
       ELSE         1024 * CAST(REPLACE(data, ' KB', '') AS INT) /
                           CAST(REPLACE(rows, ' KB', '') AS INT)
   END        AS 'Avg Bytes/Row',

   data       AS 'Data Space +',
   index_size AS 'Index(es) Space +',
   unused     AS 'Unused Space =',
   reserved   AS 'Total Space'
  FROM #SpaceUsed
 ORDER BY CAST(REPLACE(reserved, ' KB', '') AS INT) DESC

-- Done.
DROP TABLE #SpaceUsed

Mostly Microsoft SQL Server Stuff

This blog will mostly be a place to publish my SQL Server T-SQL scripts, in the hope that someone else will find them useful. Currently, the focus is on SQL Server 2000, but hopefully that will change soon.