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:
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.
[code language=”SQL” light=”true”]
— Author: Anant Anand Gupta
— Date: 2010-05-10
— Description: Return the number of
— records in the Table
<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]
<p>CREATE PROCEDURE [dbo].[sp_GetCount] @TableName NVARCHAR(776)
SET NOCOUNT ON
DECLARE @str NVARCHAR(1000)
SET @str = ‘Select Count(*) [Record Count] From ‘ + @TableName
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>’
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.
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 !!!