Script to find the total size of all the databases in SQL Server.

Below is the script that will give you the total size of all the databases present in SQL Server.


create table #dbsize ( size_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
   SELECT [name]
   FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
   BEGIN
       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbsize( size_mb )
select size/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
       Exec (@SQL)
       FETCH NEXT FROM curDB INTO @DB
   END
 
CLOSE curDB
DEALLOCATE curDB
select SUM(size_mb) from #dbsize
drop table #dbsize