Thursday, April 21, 2005

Viewing the source code of SP in Yukon

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

sys.sp_helptext ProcedureName
sp_helptext ProcedureName

System stored procedure

In Sql server 2000 to view the source code of system stored procedure we use the following syntax

use master
sp_helptext sp_help

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'

1 comment:

Anonymous said...