Database used and free space

--Here's a script to check the free and used space for a single database.

use <DB Name>
go
select sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CAST(sysfiles.size/128.0 AS int) AS FileSizeMB, CAST(CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed') AS int)/128.0 AS int) AS SpaceusedMB,
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)) --FROM dbo.sysfiles
+ '%' AS FreeSpacePct  
from dbo.sysfiles
=======================================================================

alter database databasename modify file ( name= 'filename',size=total size)
=======================================================================

--Script to check all databases used and free space on a drive.

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], 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) As FreeSpace
    From sys.database_files'
Select * From ##temp where physical_name like 'F:\%' order by FreeSpace desc
drop table ##temp
=======================================================================