Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_spaceused Expand / Collapse
Author
Message
Posted Friday, October 24, 2008 5:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:10 AM
Points: 318, Visits: 1,447
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
Post #591112
Posted Friday, October 24, 2008 6:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:10 AM
Points: 318, Visits: 1,447
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'"
Post #591139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse