How to find the size of every database in SQL Server 2000

if you want to find all SQL Server 2000 database sizes at once, you have to use sp_spaceused for all databases. It takes some time to write all those T-SQL statments. This handy SQL script will find the size of every database in SQL Server 2000 without using the sp_spaceused function. It will save you time as well as the server's time.

CREATE PROCEDURE Usp_FindAllDBSizes
AS
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0

SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @counter > 0
BEGIN
 SELECT @dbname=name FROM master..sysdatabases WHERE [email protected]
 TRUNCATE TABLE sizeinfo
 EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
 SELECT @counter1=MAX(fileid) FROM sizeinfo
 WHILE @counter1>0
 BEGIN
  SELECT @size=filesize FROM sizeinfo WHERE [email protected]
  SET @[email protected][email protected]
  SET @[email protected]
 END
 SET @[email protected]
 SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
 SET @size1=0.0
END
SET NOCOUNT OFF