This assumes a convention where databases are named after the responsible party with an underscore and a version number or description. It also assumes that running this script is faster than remoting to the server and looking at the directory. 😉
declare @dbname varchar(255),
@CurrentRow int,
@RowsToProcess int,
@SQL nvarchar(max),
@size float
CREATE TABLE #dbs
(
RowID int not null primary key identity(1,1),
dbname varchar(255),
owner varchar(80),
size float
)
insert into #dbs (dbname) select name from sys.databases where name not in ('master','msdb','model','tempdb')
set @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@dbname=dbname
FROM #dbs
WHERE RowID=@CurrentRow
set @SQL = 'update #dbs set size=(SELECT sum(size) FROM ' + @dbname + '.sys.database_files) where dbname=''' + @dbname + ''''
EXEC SP_EXECUTESQL @SQL
set @SQL = 'update #dbs set owner=(SELECT CASE WHEN CHARINDEX(''_'', dbname) > 0 THEN LEFT(dbname, CHARINDEX(''_'', dbname)-1) ELSE dbname END AS Owner FROM #dbs where dbname=''' + @dbname + ''') where dbname=''' + @dbname + ''''
EXEC SP_EXECUTESQL @SQL
END
select owner, COUNT(owner) as DBCount, ((((SUM(size) * 8192)/1024)/1024)/1024) as TotalGB
from #dbs group by owner
drop table #dbs
