Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Sunday, 9 April 2017

T SQL Script to find Total Datbase/DB size in MB and GB and Total Database/DB Count

T SQL Script to find Total DB size in MB and GB and Total Database Count


Create Table #DBDETAILS(Name Char(100),db_size char(30),owner char(100),dbid int,created char(30),
status nvarchar(max),comptlevel int)

Insert into #DBDETAILS
Exec master.dbo.sp_helpdb

Create Table #DBDETAILScount(Name Char(100),db_int float)


insert into #DBDETAILScount

Select name,left(db_size,10) db_int from #DBDETAILS

Create Table #DBCOUNTGB(dbsum float,[dbcount] int)

insert into #DBCOUNTGB

Select sum(db_int)[dbsum],count(name) [dbcount]
from #DBDETAILScount


select @@servername as ServerName,Round([dbsum],0) [Total DB Size in MB],Round([dbsum]/1024,0) as [Total DB Size in GB],dbcount as [db count] from #DBCOUNTGB


drop table #DBDETAILS

drop table #DBDETAILScount


drop table #DBCOUNTGB