Using FileProperty function

• Elliott Berkihiser

  SSCarpal Tunnel

  Points: 4068

  More actions

  June 6, 2007 at 3:25 pm

  #117273

  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

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  June 6, 2007 at 6:59 pm

  #711288

  Use sp_MSforeachdb.

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

  _____________
  Code for TallyGenerator

• Elliott Berkihiser

  SSCarpal Tunnel

  Points: 4068

  More actions

  June 6, 2007 at 7:45 pm

  #711294

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

  Thanks,

  Elliott

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  June 6, 2007 at 7:59 pm

  #711296

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

  

  _____________
  Code for TallyGenerator