To View a Stored Procedure with T-SQL

This article is taken from www.devguru.com

Syntax:

1. To view the definition of a stored procedure:
sp_helptext procedure_name

2. To view the information about a stored procedure:
sp_help procedure_name

3. To view the dependencies of the stored procedure:
sp_depends procedure_name

procedure_name
Is the name of the stored procedure.

SQL Server allows us to view the definition, information, and dependencies of a stored procedure.

Examples
Code:
sp_helptext spNewAvgGrade;
Output:
CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
Explanation:

In the above example, the sp_helptext displays the text of the spNewAvgGrade stored procedure.
Language(s): MS SQL Server
Code:
sp_help spNewAvgGrade;
Output:
Name Owner Type Created_datetime

spNewAvgGrade dbo stored procedure 2003-09-14 23:53:13.810



Parameter_name Type Length Prec Scale Param_order

@Course int 4 10 0 1

Explanation:

This example displays information about the stored procedure.
Language(s): MS SQL Server
Code:
sp_depends spNewAvgGrade;
Output:
In the current database, the specified object references the following:

Name Type Updated Selected Column

dbo.Students user table no no Std_Course
dbo.Students user table no no Std_Grade

Explanation:

This example shows the dependencies of the stored procedure.
Language(s): MS SQL Server