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 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 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
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.