Script To find files used space in SQL Server

The following script will give you the space used for all files in server:

create  TABLE  #DBInfo
(  
DatabaseName VARCHAR(100), 
FileSizeMB INT, 
LogicalFileName sysname, 
PhysicalFileName NVARCHAR(520), 
FreeSpaceMB INT, 
FreeSpacePct VARCHAR(7), 
FreeSpacePages INT, 
PollDate datetime) 


DECLARE @command VARCHAR(5000) 
SELECT @command = 'Use [' + '?' + '] SELECT 

' + '''' + '?' + '''' + ' AS DatabaseName, 
CAST(sysfiles.size/128.0 AS int) AS FileSize, 
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, 
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, 
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, 
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) 
AS decimal(4,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct, 
GETDATE() as PollDate FROM dbo.sysfiles' 
INSERT INTO #DBInfo 
   ( 
   DatabaseName, 
   FileSizeMB, 
   LogicalFileName, 
   PhysicalFileName, 
     FreeSpaceMB, 
   FreeSpacePct, 
   PollDate) 
EXEC sp_MSForEachDB @command 

SELECT 

   DatabaseName, 
   FileSizeMB, 
   LogicalFileName, 
   PhysicalFileName, 
    FreeSpaceMB, 
   FreeSpacePct, 
   PollDate 
FROM #DBInfo 
ORDER BY 

   DatabaseName 

drop table #DBInfo