Database File Size Stats

--Using original Query:

 select DB_NAME(database_id)as dbname, m.name
, m.physical_name
, m.type_desc
, m.size / 128.0 "Initial Size (MB)"
, d.size / 128.0 "current Size (MB)"
, (fileproperty(m.name, 'spaceused'))/128.0 "Used (MB)"
from sys.master_files m, sys.database_files d
where database_id = DB_ID()and m.file_id = d.file_id

--Using sp_MSforeachdb:

exec sp_MSforeachdb
N'USE ? select DB_NAME(database_id) as dbname, m.name, m.physical_name, m.type_desc, m.size / 128.0 "Initial Size (MB)"
, d.size / 128.0 "current Size (MB)", (fileproperty(m.name, ''spaceused''))/128.0 "Used (MB)"
from sys.master_files m, sys.database_files d
where database_id = DB_ID() and m.file_id = d.file_id'

--Using Cursor:

 IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable; 

PRINT'check for user database files and sizes' 

Create table #TempTable
(file_id int,db_name varchar(200),file_name varchar(200),physical_name varchar(200),type_desc varchar(20),initial_size_mb float,current_size_mb     float,used_mb       float) 

DECLARE @Database VARCHAR(50)DECLARE @sql VARCHAR(1000)

DECLARE databases_cursor CURSOR FOR       

SELECT name
FROM sys.databases      WHERE name NOT IN('model','msdb','questsoftware','litespeedlocal') ORDER BY name 
OPEN databases_cursor
FETCH NEXT FROM databases_cursor INTO @Database
 IF @@FETCH_STATUS <> 0
      PRINT '         <>' WHILE @@FETCH_STATUS = 0
BEGIN    
      PRINT '   - ' + @Database         SET @sql = 'USE ' + @Database + ' ;'         
SET @sql = @sql + 'SELECT m.file_id as file_id, '      
SET @sql =@sql + 'DB_NAME(m.database_id) as DBNAME, '         
SET @sql = @sql + 'm.name as name, '      
SET @sql = @sql + 'm.physical_name as filename, '         
SET @sql = @sql + 'm.type_desc as Type, '      
SET @sql = @sql + '(m.size / 128.0) as [inital-size-mb], '      
SET @sql = @sql + '(d.size / 128.0) as [current-size-mb], '         
SET @sql = @sql + '((fileproperty(m.name, ''spaceused''))/128.0) as [used-mb] '      
SET @sql = @sql + 'FROM ' + @Database + '.sys.master_files as m' + ', ' + @Database + '.sys.database_files as d '      
SET @sql = @sql +'where m.file_id = d.file_id and DB_NAME(m.database_id) = ''' + @Database +''''         
Print @sql    
         insert into #TempTable         
EXEC (@sql)       
FETCH NEXT FROM databases_cursor INTO @Database
END select * from #TempTable; drop table #TempTable; 
CLOSE databases_cursor
DEALLOCATE databases_cursor