Skip to content
Related Articles

Related Articles

Some Useful System Stored Procedures in SQL
  • Last Updated : 07 Dec, 2020
GeeksforGeeks - Summer Carnival Banner

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 :

NameSalaryCity
Ankit24500Delhi
Babita23600Noida
Chetan25600Noida
Deepak24300Delhi
Isha25900Delhi
Khushi24600Noida
Megha25500Noida
Parul23900Noida

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 :

NameCitySalary
BabitaNoida23600
ParulNoida23900
DeepakDelhi24300
NameCitySalary
IshaDelhi25900
ChetanNoida25600
MeghaNoida25500

Example of sp_help Stored Procedures :

SP_HELP SelectGeek ;

Output :

NameOwnerTypeCreated_datetime
SelectGeekdbostored procedure2020-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 :

nametypeupdatedselectedcolumn
dbo.geek_demouser tablenoyesName
dbo.geek_demouser tablenoyesSalary
dbo.geek_demouser tablenoyseCity

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :