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?