sp_spaceused alternative in SQL 2000

Question

Hi,

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

Answer 2

You can always do sp_helptext sp_spaceused and see for yourself what it does, where from does it gets the data. You can also run adn display the execution plan, to pretty much the same result.

As far as I remember in SQL 2000 the space used is retrieved from info in sysindexes.

Answer 3

Not needed anymore.

Still It would be good to learn about it "someday"