How to find the size of every database in SQL Server 2000
This SQL script will find the size of every database in SQL Server 2000 without using the sp_spaceused function.
You can find out how much space a database is occupying on the hard disk by using the sp_spaceused function. However,...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
Start the conversation
0 comments