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
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
Messages -------- Command(s) completed successfully.
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.
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_helptext Stored Procedures in SQL Example
In this example, we use the sp_helptext stored procedure against the previously created procedure.
As you can see from the below screenshot, SQL sp_helptext is displaying the procedure definitions as a text.
sp_depends Stored Procedures in SQL Example
In this section, we use the sp_depends stored procedure against the previously created procedure.
Let us see the output of SQL sp_depends. All the columns used in a stored procedure depends upon the Employee Duplicates.