sp_spaceused alternative in SQL 2000

Question

Like we have alternative for sp_helpdb sp = sysdatabases table,

Do we have an alternative table for sp_spaceused sp in SQL server 2000?

Regards

Manjot

Answer 1

Not really - SQL Server 2005 introduces a huge improvement in systems management with the "sys" system catalog schema and the Dynamic Management Views - but in SQL Server 2000, you're quite limited.

You can use this script to check and collect the space used information and present it nicely - but it's still using the sp_spaceused stored proc in the end:

--************************************** 
-- Name: Get SQL Table Size - Table and Index Space - Row Count 
-- Description: This Script will return the row count and the amount of 
-- disk space that each table uses within a specifed database. 
-- When returning total disk space used, it breaks it up into 3 categories... 
-- 1. The amount used by data 
-- 2. The Amount used by indexes 
-- 3. The amount of unused space 

SET NOCOUNT ON 

DECLARE @cmdstr varchar(100) 
DECLARE @Sort bit 

SELECT @Sort = 0 /* Edit this value for sorting options */ 

--Create Temporary Table 
CREATE TABLE #TempTable 
    ([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) ) 

--Create Stored Procedure String 
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"''' 

--Populate Tempoary Table 

INSERT INTO #TempTable 
    EXEC(@cmdstr) 

--Determine sorting method 

IF @Sort = 0 BEGIN 
    --Retrieve Table Data and Sort Alphabetically 
    SELECT * FROM #TempTable 
    ORDER BY Table_Name 
END 
ELSE BEGIN /*Retrieve Table Data and Sort by the size of the Table*/ 
    SELECT * 
    FROM #TempTable 
    ORDER BY Table_Size DESC 
END 

--Delete Temporay Table 
DROP TABLE #TempTable