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'