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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy