Today, we will look at one of the obscure functions of Sql Server. It is quite useful in certain situations.

FILEPROPERTY: This function allows us to query certain properties of a given database file. For example:

• Space Used
• Is this a Primary file or a Log file
• …etc

[sourcecode language=”sql”]

USE [tempdb]
GO

SELECT FILEPROPERTY(‘tempdev’, ‘IsPrimaryFile’) AS [Is Primary File]
SELECT FILEPROPERTY(‘templog’, ‘SpaceUsed’) AS [Number Of Pages Allocated]
GO

[/sourcecode]

Results of FILEPROPERTY Query

Another example:

[sourcecode language=”sql”]
EXEC sp_msforeachdb ‘use ?; Select ”?” DBName, Name FileNme, fileproperty(Name,”SpaceUsed”) SpaceUsed from sysfiles’
GO

[/sourcecode]

FILEPROPERTY with sp_msforeachdb

Hope this helps,

_SqlTimes