T-SQL » Stored Procedures » To View a Stored Procedure

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:
NameOwnerTypeCreated_datetime
spNewAvgGrade dbo stored procedure 2003-09-14 23:53:13.810






Parameter_name Type Length Prec Scale Param_order
@Courseint41001






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 TypeUpdatedSelectedColumn
dbo.Studentsuser tablenonoStd_Course
dbo.Studentsuser tablenonoStd_Grade





Explanation:

This example shows the dependencies of the stored procedure.

Language(s): MS SQL Server