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

You can find out how much space a database is occupying on the hard disk by using the sp_spaceused function. However,...

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 dbid=@counter
 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 fileid=@counter1
  SET @size1=@size1+@size
  SET @counter1=@counter1-1
 END
 SET @counter=@counter-1
 SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
 SET @size1=0.0
END
SET NOCOUNT OFF