Introduction
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:
CREATE PROCEDURE sp_FindTableNames
(
@ColumnName varchar(128)
) AS
SELECT *
FROM sysobjects
WHERE id IN
(
SELECT id
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'
Caution
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.
Conclusion
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.
