- This article is taken from www.devguru.com
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
this applies for T-SQL only
ReplyDelete