Know all Database sizes in a server.

,

We can know how much space a database is occupied in hard disk by using sp_spaceused function. If we want to find all database sizes at a time, we have to provide use  and sp_spaceused for all databases. It takes some time to write all those T-sql statments. My script will find each and every database size with out using sp_spaceused function. It will save your time and server time. Try this,

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

Rate

3 (2)

Share

Share

Rate

3 (2)