Syndicated/ Tips and Tricks

Table Space Calculation, Redux

2 Comments

A fellow ASSp (Accomplished SQL Server Professional) asked me if there was a SQL 2000 version of the Table Space Calculation code I posted the other day.  The answer is yes.  It only required that I change the @Results table variable to a temp table, #Results.  This will work on SQL 2000, 2005 and 2008.

While I was at it, I modified the code to report back results in Mb.  (Who the heck cares about Kb anymore, come on!?!) 

You will notice that I include a formula in the ORDER BY clause (not a big fan of that, no letters please!)  I did so

  • (A) Because I assume you want to see results in this order.
  • ( Because this is a script that will be run infrequently and the result set should be small.
  • (X) Because it’s my blog and I can do as I please. (Note my numbering convention as proof.)

So, here it is for your review.

DECLARE @TableName VARCHAR(100) 
CREATE TABLE #Results (name VARCHAR(100), rows bigint, reserved_KB VARCHAR(50), data_KB VARCHAR(50), index_size_KB VARCHAR(50), unused_KB VARCHAR(50)) 
DECLARE curTables CURSOR FOR 
SELECT TABLE_CATALOG + ‘.’ + TABLE_SCHEMA + ‘.’ + TABLE_NAME    –, * 
FROM INFORMATION_SCHEMA.tables 
WHERE TABLE_TYPE = ‘BASE TABLE’ 
ORDER BY TABLE_NAME 
OPEN curTables 
FETCH NEXT FROM curTables INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
INSERT INTO #Results([name], [rows], [reserved_KB], [data_KB], [index_size_KB], [unused_KB]) 
EXEC sp_spaceused @TableName, false 
FETCH NEXT FROM curTables INTO @TableName 
END 
CLOSE curTables 
DEALLOCATE curTables 
SELECT  [name], 
        [rows], 
        LEFT([reserved_KB],LEN([reserved_KB])-3)*8/1024 AS reserved_MB, 
        LEFT([data_KB],LEN([data_KB])-3)*8/1024 AS data_MB, 
        LEFT([index_size_KB],LEN([index_size_KB])-3)*8/1024 AS index_size_MB, 
        LEFT([unused_KB],LEN([unused_KB])-3)*8/1024 AS unused_MB 
FROM #Results 
ORDER BY LEFT([reserved_KB],LEN([reserved_KB])-3)*8/1024 DESC 
        
DROP TABLE #Results