Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_spaceused


sp_spaceused

Author
Message
Henry_Lee
Henry_Lee
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 1658
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
Henry_Lee
Henry_Lee
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 1658
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'"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search