Use FILEPROPERTY to find free space in all the database

The use of SpaceUsed property of the FILEPROPERTY function gives how much space is used also we can derive lot of other attributes of it such as free space and percentage of free space.

For other versions of SQL you can refer the below SQL.

DECLARE @command VARCHAR(5000)   
DECLARE @DBSpaceInfo TABLE   
( ServerName VARCHAR(100),   
DatabaseName VARCHAR(100),   
PhysicalFileName NVARCHAR(520),   
FileSizeMB DECIMAL(10,2),   
SpaceUsedMB DECIMAL(10,2),   
FreeSpaceMB DECIMAL(10,2), 
FreeSpacePct varchar(8) 
) 
 
SELECT @command = 'Use [' + '?' + '] SELECT   
@@servername as ServerName,   
' + '''' + '?' + '''' + ' AS DatabaseName   , name 
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB 
    , convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB 
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB, 
    CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct 
from sys.database_files a' 
 
INSERT INTO @DBSpaceInfo 
EXEC sp_MSForEachDB @command   
 
SELECT * from @DBSpaceInfo 

For SQL 2000 you can refer the below sql  using sysfiles system table.

DECLARE @command VARCHAR(5000)   
DECLARE @DBInfo TABLE   
( ServerName VARCHAR(100),   
DatabaseName VARCHAR(100),   
PhysicalFileName NVARCHAR(520),   
FileSizeMB DECIMAL(10,2),   
SpaceUsedMB DECIMAL(10,2),   
FreeSpaceMB DECIMAL(10,2), 
FreeSpacePct varchar(8) 
) 
 
SELECT @command = 'Use [' + '?' + '] SELECT   
@@servername as ServerName,   
' + '''' + '?' + '''' + ' AS DatabaseName   , filename 
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB 
    , convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB 
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB, 
    CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct 
from dbo.sysfiles a' 
 
INSERT INTO @DBInfo 
EXEC sp_MSForEachDB @command   
 
SELECT * from @DBInfo

space

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in databases_files, FileProperty, sp_msforeachDB, SQL, sysfiles and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s