How do I view the code for system objects?|
Updated: 6/20/2008 12:25:00 AM
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:
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:
Another is to use the new OBJECT_DEFINITION() function, which takes OBJECT_ID() as a parameter. Try this:
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:
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:
However, there is a way around this:
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.
|© 2004-2013 Aaron Bertrand, All Rights Reserved. SQL Server 2005, of course, belongs to Microsoft.|