To View a Stored Procedure

To View a Stored Procedure

Syntax:

To view the definition of a stored procedure:

sp_helptext procedure_name

To view the information about a stored procedure:

sp_help procedure_name

To view the dependencies of the stored procedure:

sp_depends procedure_name

procedure_nameIs 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