A quick look at all database files -- location, size and freespace

This is a very fast one.  Just something I ran today to quickly review all database file details on the server.  Well, not necessarily all details, but the ones that I needed --- location, size and freespace for each.  Fast and easy --> 

IF OBJECT_ID('tempdb..##tempfiles') IS NOT NULL

DROP TABLE ##tempfiles;

CREATE TABLE ##temp

(

    DatabaseName sysname,

    FileName sysname,

    PhysicalFileName nvarchar(500),

    FileSize decimal (18,2),

    FreeSpace decimal (18,2)

)  

EXEC sp_msforeachdb '

USE [?];

INSERT ##temp (DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace)

SELECT DB_NAME(), Name, physical_name, CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) AS NVARCHAR) Size,

 CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS DECIMAL(18,2)) AS NVARCHAR) FreeSpace

FROM sys.database_files

'

SELECT

       DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace

FROM ##temp

DROP TABLE ##temp;

Sample output:

You either MUST ignore the C drive location of those, files, or please just remember that it is a bad idea to locate any of your databases on the C drive -- system or otherwise.

They are just there for a fast sample on this code.  Check it out and let me know what you think.

Happy SQL.