SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Creating a System Stored Procedure

By Robert Marda,


In this article I will show you how you can make your own system stored procedure. Simply put, a system stored procedure is any stored procedure with a name that starts with sp_ that is found in the master database. These SP’s can be executed from any database and will run from the context of that database. Any time you execute an SP that starts with sp_ SQL Server goes directly to the master database to find it.

Why Make A System Stored Procedure

I resort to making my own system stored procedure if I can’t find a way to do what I need to do and I want to be able to do it in any database. Before SQL Server 2000 was released I created a stored procedure that could script any table with its corresponding non clustered indexes and primary key. I later designed it to handle default values and clustered indexes. I placed it in the master database so that I could use it in any database and yet only have to maintain one stored procedure. I’m sure there are other reasons. To me you make whatever you need to achieve your companies mission without compromising the integrity of your SQL Servers.

Pit falls

The only problems I am aware of with placing a stored procedure in the master database are: 1. You must be careful to not change anything while in the master database. 2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server. 3. You must be sure you only give access to these SP’s to as few people as possible. The ones I have created and fielded have only been used by people with sa permissions on that SQL Server.

A Simple System Stored Procedure

The below stored procedure will give you all the table names that have a specified column name in them:
@ColumnName varchar(128)
) AS

FROM sysobjects
	FROM syscolumns
	WHERE name = @ColumnName
AND xtype = 'U'
Create this stored procedure in your master database on a development SQL Server. Now execute the following code from the Northwind database:
EXEC sp_FindTableNames @ColumnName = 'employeeID'


Whenever using system tables you must keep in mind that Microsoft could change the table structure at any time. If they do then you might have to revise all the system stored procedures you create.


I have not found many reasons to create system stored procedures. However, I believe that creating your own can be useful and with proper testing will not compromise your SQL Servers.
Total article views: 12294 | Views in the last 30 days: 3
Related Articles

System Stored Procedures referring to which tables.??

System Stored Procedures Tables?


System Stored Prodecures

New Proc Created in Master under System Stored Procedures


Custom Stored Procedures in MSDB

Is it OK to create custom stored procedures in the MSDB system database


SQL Server – Marking a stored procedure as system object

Marking a stored procedure as system object allows to run the procedure in a user database context. ...

advanced querying    
sql server 7