June 6, 2007 at 3:25 pm
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
June 6, 2007 at 6:59 pm
Use sp_MSforeachdb.
Include 'USE ?' into the command string you execute.
_____________
Code for TallyGenerator
June 6, 2007 at 7:45 pm
Ah yes. I had forgotten about that one. Do you think Microsoft is going to deprecate sp_MSforeachdb anytime soon?
Thanks,
Elliott
June 6, 2007 at 7:59 pm
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