This post is to find the database file usage details of all databases of an instance. The query runs on all versions of sql 2000/2005/2008/R2 . The requirement is to use same query to be executed it across all version of SQL SERVER.  We can  also do this using dynamic SQL’s.

Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.

Download here -Database_File_Usage_Details

***************************

USE MASTER

Go

CREATE table ##temp

(

Name varchar(100),

groupid int,

SizeMB decimal(10,2),

SpaceUsed decimal(10,2),

FreeSpace decimal(10,2)

)

EXEC master..sp_MSForeachdb ‘

USE ?

BEGIN

insert into ##temp(Name,GroupId,SizeMB,SpaceUsed, FreeSpace)

SELECT ”[''+''?''+'']” as databasename,groupid

, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB

, CAST(FILEPROPERTY(name, ”SpaceUsed”)/128.0 as DECIMAL(10,2)) as Space_Used

, CAST(size/128.0-(FILEPROPERTY(name, ”SpaceUsed”)/128.0) AS DECIMAL(10,2)) AS Available_Space

FROM sysfiles

END

‘

–select * from ##temp

SELECT @@SERVERNAME Servername,

CONVERT(VARCHAR(50), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],

(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,

–(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],

(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,

–(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],

(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid!=0) AS [DataMB],

(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid=0) AS [LogMB],

(SELECT SUM(SpaceUsed) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid!=0) AS [DataSpaceUsedMB],

(SELECT SUM(SpaceUsed) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid=0) AS [LogSpaceUsedMB],

(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid!=0) AS [FreeDataSpaceMB],

(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid=0) AS [FreeLogSpaceMB],

(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid!=0) +(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid=0)

AS [TotalFreeSpaceMB],

(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid!=0)+(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘['+DB.name+']‘ AND groupid=0) TotalSizeMB

FROM master.dbo.sysdatabases DB where DATABASEPROPERTYEX(name, ‘Status’) =’Online’

ORDER BY dbName

drop table ##temp

*******************************************************************************

Output -