Useful System Stored Procedures in SQL

In this article we will show you, some of the Useful System Stored Procedures in SQL. These procedures will help you to extract the definition, and dependencies of the existing stored procedures. I suggest you to refer Introduction to Stored Procedure article to understand the basics of the stored procedure.

Following three stored procedures are very useful to deal with user defined procedures:

• sp_help : This procedure will show you the Stored procedure Name, Schema Name, created date and Time. If there are any parameters then, Parameter Name, Data Type, Length, Precision, Scale, Collation etc.
• sp_helptext : Displays the definition of the stored procedure. if the procedure is encrypted then this sp_helptext will not return any definition.
• sp_depends : It will show all the dependencies. This is useful to check on which table the stored procedure is depending on.

For this demonstration, We are going to use the below shown SQL table

Useful System Stored Procedures in SQL Example

Before we get into the system stored procedures, let me create a simple stored procure that holds two Select statements inside a stored procedure.

USE [SQL Tutorial]
GO
CREATE PROCEDURE SelectEmployees
AS
BEGIN
	SELECT TOP 6 [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education], [Occupation], [YearlyIncome], [Sales], [HireDate]
	FROM [EmployeeDuplicates]
	ORDER BY [Sales] ASC
	
	SELECT TOP 4 [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education], [Occupation], [YearlyIncome], [Sales], [HireDate]
	FROM [EmployeeDuplicates]
	ORDER BY [Sales] DESC
END
GO

OUTPUT

Let me show you the data that it was returning

sp_help Stored Procedures in SQL Example

In this example we will use the sp_help stored procedure against the previously created procedure.

SP_HELP SelectEmployees

OUTPUT

Let me use another procedure which contains the Parameters as well. I suggest you to refer Input Parameters in a Stored Procedure article to understand the below specified stored procedure.

SP_HELP SelectEmployees

OUTPUT

sp_helptext Stored Procedures in SQL Example

In this example we will use the sp_helptext stored procedure against the previously created procedure.

SP_HELPTEXT SelectEmployees

As you can see from the below screenshot, it is displaying the procedure definitions as a text.

sp_depends Stored Procedures in SQL Example

In this example we will use the sp_depends stored procedure against the previously created procedure.

SP_DEPENDS SelectEmployees

All the columns that are used in a stored procedure depends upon the Employee Duplicates.

Thank you for Visiting Our Blog