Some Useful System Stored Procedures in SQL Server 2012

This article provides a brief introduction to various system Stored Procedures in SQL Server.

This article provides a brief introduction to various system Stored Procedures in SQL Server. These Stored Procedure are already defined in SQL Server. These procedure start with the sp_ prefix. These are physically stored in a hidden SQL Server Resource Database and logically appear in the sys schema of each user defined and system defined database. Hence we don't use this prefix when naming user-defined procedures. Let's take a look at a practical example of the most commonly used system Stored Procedures. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 
Stored Procedure
 
Stored Procedures are a pre-compiled set of one or more statements that are stored together in the database. They reduce the network load because of the pre-compilation. We can create a Stored Procedure using the Create proc statement.
There are two types of procedures in SQL Server; they are:
  1. User Defined Procedures
  2. System Defined Procedures
User Defined Procedures
 
These Stored Procedures are defined by the user in SQL Server. To create a Stored Procedure use the Create proc statement.
Now create a table named UserTable with the columns UserID and UserName. Set the identity property=true for UserID. The table looks as in the following:
 
Table-in-SQL-Server.jpg
 
Example
 
Now create a Stored Procedure:
  1. Create PROCEDURE UsingExistsstoredprocedure    
  2. (    
  3.       @UserName VARCHAR(100)    
  4. )    
  5. AS    
  6. DECLARE @ResultValue int    
  7. BEGIN TRAN    
  8. IF EXISTS    
  9.     (    
  10.           SELECT * FROM UserTable    
  11.           WHERE UserName = @UserName    
  12.         )    
  13.      BEGIN    
  14.          SET  @ResultValue = -5    
  15.      END    
  16. ELSE    
  17.       BEGIN    
  18.            INSERT INTO UserTable    
  19.                (    
  20.                    UserName     
  21.                )    
  22.            VALUES    
  23.            (    
  24.                  @UserName    
  25.            )    
  26.            set @ResultValue = @@ERROR    
  27.      END    
  28. IF @ResultValue <> 0    
  29.      BEGIN    
  30.             ROLLBACK TRAN    
  31.       END    
  32. ELSE    
  33.       BEGIN    
  34.             COMMIT TRAN    
  35.       END    
  36. RETURN @ResultValue    
In the preceding Stored Procedure we declare a return variable ResultValue. "If exists" is used to check the insertion record; whether it belongs to the table or not. If the inserted record is already in the table then set the return status @ResultValue = -5 and the inserted record is not in the table by default; the successful execution of a Stored Procedure will return 0.
 
System defined procedures
 
System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Most commonly used system Stored Procedures are described below.
 

Sp_help <Object Name> 

 
The Sp_help is a system Stored Procedure that Reports information about a database object, a user-defined data type, or a data type. It will only return information for objects that are able to be referenced within the database you are currently in.
 
To Find All Database Object Information
  1. exec sp_help  
Output
 
Sp-help-in-SQL-Server.jpg
 
To Find Table Information
  1. exec sp_help 'employee'  
Output
 
Sp-help-with-table-name-in-SQL-Server.jpg
 

Sp_table<Object Name> 

 
The Sp_help is a system Stored Procedure that reports information about the list of the tables from the database. The following system Stored Procedure returns all the tables first in the result set, followed by views.
  1. EXEC sys.sp_tables  
Output
 
Sp-table-in-SQL-Server.jpg
 
The following query provides various information, from create date to file stream, and many other important information.
  1. SELECT * FROM sys.tables  
Output
 
Sp-table-with-select-in-SQL-Server.jpg 
 

sp_helptext <Object Name>

 
The sp_helptext is a system Stored Procedure that displays the definition that is used to create an object in multiple rows. The following example displays the definition of the procedure SelectUserDetail in the master database.
 
Example
  1. exec sp_helptext SelectUserDetail  
Output
 
Sp-helptext-in-SQL-Server.jpg 
 

Sp_depends <Object Name>

 
The sp_helptext is a system Stored Procedure that is used to get the dependent object details. If you are working in a large database then before changing a table or a Stored Procedure you may need to know about the tables, Stored Procedures and functions dependencies by using the sp_depends procedure.
  1. exec sp_depends Registration  
Output
 
Sp-depends]-in-SQL-Server.jpg