Where is sp_MSrepl_getpublisherinfo in SQL Server?

Question

In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find on the server. This stored procedure obviously exists, as it no errors occur regarding it not existing. Where does it live, and can its code be viewed?

Answer 1

So after more research I found that sys.sp_MSrepl_getpublisherinfo lives in a database named mssqlsystemresource, which lives in Sql Server's binn folder. This database is invisible to SSMS, but you can copy and attach it as a different name to have full access. This process is described here:

http://www.sqlnewsgroups.net/sqlserver/t21348-browse-mssqlsystemresource-mdf-sql-servers-resource-database.aspx

I have copied that text here, in case that page goes away in the future:

Giving credit where credit is due, I found the following info I am about to post at the following location:

http://www.aspfaq.com/sql2005/show.asp?id=28

Since the engine has hooks that hide the mssqlsystemresource database from users, you don't have direct access to it through the GUI.

However, there is a way around this:

1. Determine where the system databse files live, and keep this path handy:

   USE master
   GO
   SELECT REPLACE(filename, 'master.mdf', '') 
     FROM sys.sysfiles 
    WHERE fileid = 1
   

2. Stop the SQL Server service;

3. Copy the files mssqlsystemresource.*df -> resource_copy.*df. NOTE: do not rename or remove the mssqlsystemresource files!

4. Start the SQL Server service;

5. Run the following code in a new query window:

   EXEC sp_attach_db 
     'Resource_Copy', 
     '<path from above>resource_copy.mdf', 
     '<path from above>resource_copy.ldf'
   

6. Now, the system will no longer identify this database as a "special" database

7. You can easily scan through the names of system objects that might otherwise be unknown (and browse any code associated with these objects) from a GUI (like SQL Server Management Studio).