October 24, 2008 at 5:54 am
Good day all,
I have a question about sp_spaceused, but I wonder if the question actually pertains to all system sp's.
I'm trying to get the size for all db's on the server (SS2000 SP4). This works fine: EXEC sp_msforeachdb 'USE ? EXEC sp_spaceused'.
However, I'd like to tweak sp_spaceused so it outputs on line line, so I can insert it into a table. I'd rather not alter the original proc, so I created a new one called sp_spaceused_modified. Before I made any changes to my new proc, I figured I'd test it: EXEC sp_msforeachdb 'USE ? EXEC sp_spaceused_modified'.
When I test it, the database size is reported incorrectly. Every other field is returned correctly - unallocated space, reserved, data, index_size, and unused. However, for each database on the server the size is reported as the size of the master db.
Again, I tested the new proc BEFORE I made any changes to it. I ran both from the master database in QA, they're both owned by dbo, I'm a sysadmin on the box. However, in EM I noticed the new proc I created is of type "User", while the original is of type "Sytem".
So, my question is this: Is there something inherent in that "type" designation that is causing the 2 procs to behave differently? If so, can I change that designation?
Thanks
October 24, 2008 at 6:29 am
Ok, I found the answer in this post: http://weblogs.asp.net/anjanaram/archive/2005/05/03/405434.aspx. Thank you AnjanaRam for this post.
from the post:
"For other system tables , it really doesn't matter if the stored procedure gets listed as 'user' or 'system' type. We would only need to create the Stored Procedure in master database with a prefix as 'sp_' and it works. But in order to access the sysfiles table from the right database, we need to explicitly change it to 'system' type.
And this can be done by running the Stored procedure ' sp_MS_marksystemobject.'
Execute sp_MS_marksystemobject 'system stored proc name'"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply