Open In App

Some Useful System Stored Procedures in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 07 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads