Our Blog

SQL Server 2005 – Where the $%#@ is that stored proc ?

Reading time Less than a minute

While doing some prodding on SQL Server, i came across this newness (of course this is probably old hat to many SQL2005 dba’s)

Essentially i was tryign to track down something in sp_addserver.

The source of this stored proc [System Databases\Master\System Stored Procedures\sys.sp_addserver] showed that another stored proc called: sys.sp_MSaddserver_internal was being called.

For the life of me though, i could not track down sys.sp_MSaddserver_internal.

Turns out the answer is reasonably well documented [SQL Books Online], with 2005 – MSFT moved stored procs / and friends into a readonly hidden db. This can be made visible by copying the physical .mdf files and attaching them. [Process reasonably documented on the interwebs if you know what to search for]

This effectively will allow you to do a:

use Resource_Copy
select name from sys.objects where name like ‘%MS%internal%’

to reveal the missing procs for you to examine/tinker with