Useful System Stored Procedures in SQL 4

Useful System Stored Procedures in SQL

Let us see 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. The following three Sql Server System stored procedures are 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. It is useful to check on which table the stored procedure depends.

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

Useful System Stored Procedures in SQL 1

I suggest you to refer Introduction to Stored Procedure article to understand the basics of the SQL stored procedure.

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.

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

Run query

Messages
--------
Command(s) completed successfully.

Let me show you the data that it was returning

Useful System Stored Procedures in SQL 6

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
Useful System Stored Procedures in SQL 3

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

SP_HELP SelectEmployees
Useful System Stored Procedures in SQL 7

sp_helptext Stored Procedures in SQL Example

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

SP_HELPTEXT SelectEmployees

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

Useful System Stored Procedures in SQL 4

sp_depends Stored Procedures in SQL Example

In this section, we use the sp_depends stored procedure against the previously created procedure.

SP_DEPENDS SelectEmployees

Let us see the output of SQL sp_depends. All the columns used in a stored procedure depends upon the Employee Duplicates.

Useful System Stored Procedures in SQL 5