T-SQL » Stored Procedures » 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
- To view the definition of a stored procedure:
- 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
