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.
-- ====================================== -- 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
Now you can execute the stored procedure in any database you want like this:
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 !!!