Some Useful System Stored Procedures in SQL

Some Useful System Stored Procedures in SQL.

These procedures are built-in procedures and will help to extract the definition and dependencies of the existing user-defined stored procedures.

• sp_help :
  This will display the Stored procedure Name, Schema Name, created date, and Time or if there are any parameters,
  then Parameter Name, Data Type, Length, Precision, Scale, Collation, etc. as result.
• sp_helptext :
  This will display the content of the stored procedure as result.
  
  
• sp_depends :
  This will show where the procedure is dependent like name of tables, functions, etc.

Example –
Let us suppose we have table named geek_demo:

Select * from geek_demo ;

Output :

Name	Salary	City
Ankit	24500	Delhi
Babita	23600	Noida
Chetan	25600	Noida
Deepak	24300	Delhi
Isha	25900	Delhi
Khushi	24600	Noida
Megha	25500	Noida
Parul	23900	Noida

Let us create a simple stored procure that holds two Select statements inside it.

CREATE PROCEDURE SelectGeek
AS
BEGIN
SELECT TOP 3 [Name], [City], [Salary]
FROM [geek_demo]
ORDER BY [Salary] ASC
SELECT TOP 3 [Name], [City], [Salary]
FROM [geek_demo]
ORDER BY [Salary] DESC
END

GO

Calling the stored procedure :

EXEC SelectGeek ;

Output :

Name	City	Salary
Babita	Noida	23600
Parul	Noida	23900
Deepak	Delhi	24300

Name	City	Salary
Isha	Delhi	25900
Chetan	Noida	25600
Megha	Noida	25500

Example of sp_help Stored Procedures :

SP_HELP SelectGeek ;

Output :

Name	Owner	Type	Created_datetime
SelectGeek	dbo	stored procedure	2020-09-29 14:59:26.943

Example of sp_helptext Stored Procedures :

SP_HELPTEXT SelectGeek ;

Output :

Text

CREATE PROCEDURE SelectGeek
AS
BEGIN
SELECT TOP 3 [Name], [City], [Salary]
FROM [geek_demo]
ORDER BY [Salary] ASC
SELECT TOP 3  [Name], [City], [Salary]
FROM [geek_demo]
ORDER BY [Salary] DESC
END

Example of sp_depends Stored Procedures :

SP_DEPENDS SelectGeek ;

Output :

name	type	updated	selected	column
dbo.geek_demo	user table	no	yes	Name
dbo.geek_demo	user table	no	yes	Salary
dbo.geek_demo	user table	no	yse	City