Using FileProperty function

  • There was an article today that described how to check the space available in database files.  I would like to iterate through each database on the server to run this code.  However, the code utilizes a function called FileProperty(Name, 'spaceused') that only operates in the context of the current database.

    Code sample:

    Select name as 'FileName', size/128.0 - CAST(FILEPROPERTY(name, 'spaceused') as int) / 128.0 as 'SpaceAvailable' from dbo.sysfiles

     

    I can use a cursor to go to each database to use the applicable SysFiles table, but how can I get around the context-sensitve issue of FileProperty()?

    Elliott

  • Use sp_MSforeachdb.

    Include 'USE ?' into the command string you execute.

    _____________
    Code for TallyGenerator

  • Ah yes.  I had forgotten about that one.  Do you think Microsoft is going to deprecate sp_MSforeachdb anytime soon?

    Thanks,

    Elliott

  • I don't think they gonna remove it from your copy of SQL Server.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply