In the early betas of SQL Server 2005, system objects were hidden away in the MSSQLSystemResource database. Regular stored procedures like sp_helptext and sp_help did not work against the system objects that we were used to peeking at to learn some of the underlying aspects of SQL Server. The following code would yield errors:

USE master
GO
--
EXEC sp_helptext 'sp_help'
--
Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 95
There is no text for object 'sp_help'.
--
EXEC sp_help 'sp_help'
--
Msg 15009, Level 16, State 1, Procedure sp_help, Line 70
The object 'sp_help' does not exist in database 'master' or is invalid for this operation.

As Clifford Dibble recently pointed out, all of the sp_help% procedures should work against system objects as they do in SQL Server 2000, and I haven't found an example.

There are some other straightfoward ways to get at the code for system objects, for example:

USE master
GO
--
SELECT definition
  FROM sys.system_sql_modules
  WHERE object_id = OBJECT_ID('sys.sp_help')

Another is to use the new OBJECT_DEFINITION() function, which takes OBJECT_ID() as a parameter. Try this:

USE master
GO
--
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_help'))

This function will only work for some objects. For example, several stored procedures are "off-limits", such as sys.sp_MScopysnapshot. Now, you might be asking, how did I figure out that there even existed such an object? You can't see it under any of the following trees:

Databases\master\Programmability\Stored Procedures\
Databases\master\Programmability\Stored Procedures\System Stored Procedures\
Databases\msdb\Programmability\Stored Procedures\
Databases\msdb\Programmability\Stored Procedures\System Stored Procedures\

The problem is that this object lives only in the MSSQLSystemResource database. Since the engine has hooks that hide this database from users, you don't have direct access to it through the GUI. Try running a simple USE command:

USE mssqlsystemresource
GO
--
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
No entry found with that name. Make sure that the name is entered correctly.

However, there is a way around this:

• 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

• Stop the SQL Server service;

  
   

• Copy mssqlsystemresource.*df -> resource_copy.*df.
  NOTE: do *not* rename or remove the mssqlsystemresource files!
   
• Start the SQL Server service;
   
• 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'

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

You still won't be able to use sp_helptext and other typical methods against many of these objects, but you can view the source of any by using right-click, 'Modify':

More importantly, you can easily scan through the names of system objects that might otherwise be unknown.