Let us look into the way of displaying the definition of user defined stored procedure and system stored procedure in Sql Server 2005 (code named yukon).
In Sql Server 2000 we would use the sp_helptext to view the source code of both user defined and system stored procedure. But that isn't completely true in Yukon.
User defined stored procedure
In Yukon to view the source code of user defined stored proc use the following code snippet
System stored procedure
In Sql server 2000 to view the source code of system stored procedure we use the following syntax
But 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'