Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating a System Stored Procedure

By Robert Marda,

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.
Total article views: 12233 | Views in the last 30 days: 13
 
Related Articles
FORUM

System Stored Procedures referring to which tables.??

System Stored Procedures Tables?

FORUM

System Stored Prodecures

New Proc Created in Master under System Stored Procedures

FORUM

Custom Stored Procedures in MSDB

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

BLOG

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. ...

Tags
administration    
advanced querying    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones