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

Using @@ROWCOUNT ( System Variable)

Using @@ROWCOUNT ( System Variable)

@@ROWCOUNT  : It is a very useful system variable that returns the number of rows read/affected by the previous statement. It’s frequently used in loops and in error handling.

Remember that it returns the number of rows affected by the previous statement. Every statement. Look at this piece of code:

Example :

BEGIN TRY

    SELECT TOP 10 * FROM sys.objects

END TRY

BEGIN CATCH

    SELECT TOP 5 * FROM sys.objects

END CATCH

SELECT @@ROWCOUNT

You might think that the result of @@ROWCOUNT would be 10 since that’s the previous command that was run. You might also think it would be 5 since that’s the last command in the code, right? However you would be wrong on both counts. The TRY/CATCH block is actually the last command, so the result of @@ROWCOUNT is 0.

SELECT TOP 10 * FROM sys.objects

Select @@ROWCOUNT

In this case, the result of @@ROWCOUNT is 10.

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View.

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View.

System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

sp_helptext can be passed any SQL Server object that contains code, for example stored procedures, views, functions, triggers etc. It then returns the code for that object.

Syntax :

sp_helptext @objname 

sp_helptext Stored Procedure or functions or Views or triggers