In SQL Server 2000, you would simply use sp_helptext to view the source code of both user-defined and system-stored procedures. But that isn’t completely true in Yukon.

To view the source code of a user-defined stored procedure in Yukon, use the following code snippet:

sys.sp_helptext ProcedureName

Or you could also use the following:

sp_helptext ProcedureName

In SQL Server 2000, you’d use the following syntax to view the source code of system-stored procedures:

use mastersp_helptext sp_help

However, sp_helptext cannot be used to display the definition of system objects in Yukon. So, for system stored procedures use the following syntax:

Select object_definition (object_id ('sys.sp_helptext')) as 'Source of Sp_helptext'