Table Space Calculation, Redux

Timothy 2 Comments Dec 1, 2008

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