Some Useful System Stored Procedures in SQL Server 2012

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:

User Defined Procedures
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:

Example

Now create a Stored Procedure:

Create PROCEDURE UsingExistsstoredprocedure
(
@UserName VARCHAR(100)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM UserTable
WHERE UserName = @UserName
)
BEGIN
SET @ResultValue = -5
END
ELSE
BEGIN
INSERT INTO UserTable
(
UserName
)
VALUES
(
@UserName
)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
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

exec sp_help

Output

To Find Table Information

exec sp_help 'employee'

Output

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.

EXEC sys.sp_tables

Output

The following query provides various information, from create date to file stream, and many other important information.

SELECT * FROM sys.tables

Output

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

exec sp_helptext SelectUserDetail

Output

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.

exec sp_depends Registration

Output