SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO USE master GO DECLARE @iCount AS INT, @iMax AS INT, @DatabaseName AS VARCHAR (200), @SQL AS VARCHAR (8000) SELECT NAME, DBID, crdate, filename, version INTO #TEMP FROM MAster..SYSDatabASES SELECT @iCount = Count(DBID) FROM #TEMP SELECT @SQL = 'Create Table ##iFile1 ( DBName varchar( 200) NULL, Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT , Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null ) ' + char(10) EXECUTE (@SQL) CREATE TABLE ##iTotals ( ServerName VARCHAR (100), DBName VARCHAR (200) NULL, FileType VARCHAR (10) , Fileid INT , FileGroup INT , TotalExtents INT , USedExtents INT , Name VARCHAR (100), vFile VARCHAR (300), AllocatedSpace INT NULL, UsedSpace INT NULL, PercentageFree INT NULL ) WHILE @iCount > 0 BEGIN SELECT @iMax = Max(dbid) FROM #TEMP SELECT @DatabaseName = Name FROM #TEMP WHERE dbid = @iMax SELECT @SQL = 'INSERT INTO ##iFile1(Fileid , FileGroup , TotalExtents , USedExtents , Name , vFile) EXEC (''USE [' + @DatabaseName + '] DBCC showfilestats'') ' + char(10) PRINT (@SQL) EXECUTE (@SQL) SELECT @SQL = 'UPDATE ##iFile1 SET DBName =''' + @DatabaseName + ''' WHERE DBName IS NULL' EXECUTE (@SQL) DELETE #TEMP WHERE dbid = @iMax SELECT @iCount = @iCount - 1 END UPDATE ##iFile1 SET AllocatedSpace = (TotalExtents * 64.0 / 1024.0), UsedSpace = (USedExtents * 64.0 / 1024.0) UPDATE ##iFile1 SET PercentageFree = 100 - CONVERT (FLOAT, UsedSpace) / CONVERT (FLOAT, AllocatedSpace) * 100 WHERE USEDSPACE > 0 CREATE TABLE #logspace ( DBName VARCHAR (100), LogSize FLOAT , PrcntUsed FLOAT , status INT ) INSERT INTO #logspace EXECUTE ('DBCC sqlperf( logspace)') INSERT INTO ##iTotals (ServerName, DBName, FileType, Name, vFile, PercentageFree, AllocatedSpace) SELECT @@ServerName, DBNAME, 'Data' AS FileType, Name, vFile, PercentageFree, AllocatedSpace FROM ##iFile1 UNION SELECT @@ServerName, DBNAME, 'Log' AS FileType, DBName, '' AS vFile, PrcntUsed, LogSize FROM #logspace -- This SQL will return result as ServerName DBName FileType Fileid FileGroup TotalExtents USedExtents Name vFile AllocatedSpace UsedSpace PercentageFree SELECT * FROM ##iTotals -- This SQL will return result as ServerName DBNAME FileType AllocatedSpaceMB SELECT ServerName, DBNAME, FileType, Sum(AllocatedSpace) AS AllocatedSpaceMB FROM ##iTotals GROUP BY ServerName, DBNAME, FileType ORDER BY ServerName, DBNAME, FileType -- This SQL will return result as ServerName DBNAME AllocatedSpaceMB SELECT ServerName, DBNAME, Sum(AllocatedSpace) AS AllocatedSpaceMB FROM ##iTotals GROUP BY ServerName, DBNAME ORDER BY ServerName, DBNAME DROP TABLE ##iFile1 DROP TABLE #logspace DROP TABLE #TEMP DROP TABLE ##iTotals