Creating System StoredProcedures in SQL Server

What is System StoredProcedures

A system StoredProcedure is a general stored procedure which started with ‘sp_’. System StoredProcedures are stored into the master database in SQL Server.

Requirement of System StoredProcedure

There are instances when you are required to perform some operations on the different databases, like finding the dependency of the table (sp_depends), you use the system StoredProcedure. There are many System StoredProcedure already provided by the SQL Server. some of the most commonly used are:

• sp_who
• sp_depends
• sp_help
• sp_helptext

Each of the above provide some pre-defined functionality, but there are chances where you want your own functionality. In that case you can create your own System StoredProcedures. Just create a StoredProcedure with prefix ‘sp_’ in the master database and you are ready to go. Here I am showing one I have created for my ease.

Example

[code language=”SQL” light=”true”]
— ======================================
— Author:      Anant Anand Gupta
— Date:        2010-05-10
— Description: Return the number of
—              records in the Table
— ======================================</p>
<p>USE master
GO</p>
<p>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetCount]’) AND type IN (N’P’, N’PC’))
DROP PROCEDURE [dbo].[sp_GetCount]
GO</p>
<p>CREATE PROCEDURE [dbo].[sp_GetCount] @TableName NVARCHAR(776)
AS
BEGIN
SET NOCOUNT ON
DECLARE @str NVARCHAR(1000)
SET @str = ‘Select Count(*) [Record Count] From ‘ + @TableName
EXEC (@str)
END
[/code]

Now you can execute the stored procedure in any database you want like this:

[code language=”SQL” light=”true” toolbar=”false”]
EXEC sp_GetCount ‘<TableName>’
[/code]

Extras

You can also create a keyboard shortcut for the above StoredProcedure to use quickly in between your SQL Code to find out the number of records in the selected table. Go to Tools -> Options -> Keyboard in SQL Server Management Studio. Select the text box in front of the predefined shortcuts which are not used. Enter the name of your StoredProcedure in the TextBox.

Click OK.

Open New Query Window (modifications are not registered with the windows already opened) for the required database. Enter the desired TableName and select the text and press your shortcut (Ctrl + 5 in my case). Here is your result

Enjoy and be more productive at your workplace !!!