By: Diana Moldovan | Comments (2) | Related: > TSQL
Problem
There have been many articles written about the issue with prefixing SQL Server stored procedures with "sp_". In this tip we will take a look at some of these issues and also some differences between SQL 2000 and SQL 2005, 2008 and 2008 R2.
Solution
Stored procedures, tables and views created in the master database prefixed with sp_ will be "visible" from any user database, you don't need to fully qualify the name in order to execute the stored procedure or access the table or view.
In this first example, we are creating a stored procedure in the master database prefixed with sp_ and then execute the SP from a user database.
USE master;
GO
IF OBJECT_ID('sp_MyUserProc') IS NOT NULL
DROP PROCEDURE sp_MyUserProc;
GO
CREATE PROCEDURE sp_MyUserProc
AS
SELECT 'I''m in master - sp_MyUserProc';
GO
-- change to one of your user databases
USE MyUserDB;
GO
EXEC sp_MyUserProc;
GO
In this next example, we will create a table in the master database prefixed with sp_ and SELECT from it from a user database.
USE master
GO
IF OBJECT_ID('sp_mytable') IS NOT NULL
DROP TABLE sp_mytable;
GO
CREATE TABLE sp_mytable(col1 tinyint);
GO
INSERT INTO sp_mytable(col1) VALUES (1), (2), (3);
/*If you execute the code against a SQL 2000 instance, please change the INSERT
statement as below:
INSERT INTO sp_mytable(col1) VALUES (1)
INSERT INTO sp_mytable(col1) VALUES (2)
INSERT INTO sp_mytable(col1) VALUES (3)*/
GO
-- change to one of your user databases
USE MyUserDB
GO
SELECT * FROM sp_mytable;
In both instances these ran without issue and this peculiarity appears in SQL 2000, 2005 and 2008.
Create a stored procedure with the same name in a user database
In this example, we will create a stored proceudre in a user database with exact same name.
-- change to one of your user databases
USE MyUserDB;
GO
IF OBJECT_ID('sp_MyUserProc') IS NOT NULL
DROP PROCEDURE sp_MyUserProc;
GO
CREATE PROCEDURE sp_MyUserProc
AS
SELECT 'I''m in an user defined DB - sp_MyUserProc';
GO
EXEC sp_MyUserProc;
GO
This time you'll see that the procedure from the user database was executed and not the procedure in the master database.
Stored Procedure marked as a System Procedure in SQL Server 2000
Another thing that can be done is to mark a stored procedure as a "system stored procedure".
The code below creates a stored procedure in master and marks it as system stored procedure using the sp_MS_upd_sysobj_category stored procedure and creates a stored procedure in a user database with the same name. Since the procedure in master was marked as a system stored procedure the procedure in master will always be executed instead of the user stored procedure.
This example illustrates this issue.
USE master;
GO
IF OBJECT_ID('sp_MySystemProc') IS NOT NULL
DROP PROCEDURE sp_MySystemProc;
GO
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO
CREATE PROCEDURE sp_MySystemProc
AS
SELECT 'I''m in master - sp_MySystemProc'
GO
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
USE TestDM
GO
IF OBJECT_ID('sp_MySystemProc') IS NOT NULL
DROP PROCEDURE sp_MySystemProc
GO
CREATE PROCEDURE sp_MySystemProc
AS
SELECT 'I''m in an user database - sp_MySystemProc'
GO
EXEC sp_MySystemProc
GO
--specify the database name
EXEC TestDM.dbo.sp_MySystemProc
If we look in Enterprise Manager we can see that sp_MySystemProc will appear as "System" even though it exists in the user database.
Even if you qualify sp_MySystemProc with the user database name as shown below, the procedure from master will still be executed.
This also shows you what is executed from running a trace with the SP:Starting and SP:Completed events. We can see that stored procedure sp_MySystemProc is executed from DatabaseID 1 which is the master database even when we specify the database name in the execution. We can also see that sp_MyUserProc is executed from DatabaseID 10 which is the TestDM database.
Here is an explanation of this behavior from SQL 2000 BOL.
SQL Server always looks for a stored procedure beginning with sp_ in this order:
- The stored procedure in the master database.
- The stored procedure based on any qualifiers provided (database name or owner).
- The stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.
Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
Stored Procedure marked as a System Procedure in SQL Server 2005 and 2008
In order to mark a stored procedure as system for SQL 2005 or later you should use sp_MS_marksystemobject.
Run the code below on an SQL 2005 or SQL 2008 instance, on behalf of an user having dbo as the default schema.
USE master;
GO
IF OBJECT_ID('sp_MyUserProc') IS NOT NULL
DROP PROCEDURE sp_MyUserProc;
GO
CREATE PROCEDURE sp_MyUserProc
AS
SELECT 'I''m in master - sp_MyUserProc';
GO
IF OBJECT_ID('sp_MySystemProc') IS NOT NULL
DROP PROCEDURE sp_MySystemProc;
GO
CREATE PROCEDURE sp_MySystemProc
AS
SELECT 'I''m in master - sp_MySystemProc';
GO
EXEC sp_ms_marksystemobject sp_MySystemProc
GO
USE AdventureWorks
GO
IF OBJECT_ID('sp_MyUserProc') IS NOT NULL
DROP PROCEDURE sp_MyUserProc;
GO
CREATE PROCEDURE sp_MyUserProc
AS
SELECT 'I''m in an user database - sp_MyUserProc';
GO
IF OBJECT_ID('sp_MySystemProc') IS NOT NULL
DROP PROCEDURE sp_MySystemProc;
GO
CREATE PROCEDURE sp_MySystemProc
AS
SELECT 'I''m in an user database - sp_MySystemProc';
GO
You can check which objects are marked as system by querying sys.objects. If a SP is "marked as system" the "is_ms_shipped" column should have a value of 1.
USE master;
SELECT [name], is_ms_shipped FROM sys.objects
WHERE [name] IN ('sp_MyUserProc', 'sp_MySystemProc')
Here we can see that the behavoir is different from SQL 2000. When we run sp_MySystemProc from a user database, in our case AdventureWorks, the stored procedure in AdventureWorks is run even though we created a system stored procedure with the same name.
The "SourceDatabaseID" column contains the ID of the database where the source code of the procedure is situated - in this case 18, i.e. "AdventureWorks". The master.dbo.sp_MySystemProc was not executed, even if it is "marked as system". This happens because beginning with SQL 2005 the name resolution mechanism changed, as explained in SQL 2008 BOL. The "sp_" prefix is used in the names of the system stored procedures, which beginning with SQL 2005 are physically located in the Resource database, but appear logically in the "sys" schema of each system or user database.
According to BOL, system stored procedures can be executed from any database without having to fully qualify the stored procedure name. When executing a user defined stored procedure whose name is not qualified the database engine will look as follows:
- in the sys schema of the current database
- in the caller's default schema if executed in a batch or in dynamic SQL
- in the calling procedure's schema and
- finally in the dbo schema of the current database.
Therefore my "marked as system" stored procedure from master has not been executed, since it is situated in the dbo schema, not in the sys schema.
Another Example with a Sytem Stored Procedure
Let's try an example featuring sp_who, which is a "genuine" system stored procedure.
USE MyUserDB;
GO
IF OBJECT_ID('MySchema.sp_who') IS NOT NULL
DROP PROCEDURE MySchema.sp_who;
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'MySchema')
DROP SCHEMA MySchema;
GO
CREATE SCHEMA MySchema;
GO
CREATE PROCEDURE MySchema.sp_who
AS
SELECT 'This is MySchema.sp_who;';
GO
IF OBJECT_ID('MySchema.sp_who') IS NOT NULL
DROP PROCEDURE MySchema.sp_who;
GO
CREATE PROCEDURE sp_who
AS
SELECT 'This is dbo.sp_who';
GO
EXEC MySchema.sp_who;
GO
EXEC sp_who;
This time I placed one of my user procedures in schema MySchema and I qualified the procedure name with the schema. This way I avoid the confusion between Microsoft's sp_who and my sp_who.
If the stored procedure is a system SP, it will be found in the sys schema. Therefore, if you create for example your own "sp_who" procedure and try something like "EXEC sp_who" from within a user database, the system procedure will be executed. Bear in mind that a user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed. The "genuine" system stored procedure, situated in the sys schema will be resolved before the unqualified sp_who.
Notice that the SourceDatabaseID for "sp_who" is 32767, which is the ID of the Resource database.
Prefixing a stored procedure name with "sp_" is an easy choice and may seem suitable at a first glance, but most of the time it's better to avoid the "sp_" prefix for user defined stored procedures.
However, there are situations when you may want to prefix your procedure with sp_. If you need a procedure from the master database to be executed in the context of a user database, you'll have to prefix the procedure with sp_ (so that it's "visible" from any database) and mark it as system. Read this tip to learn more about creating your own system stored procedure.
Next Steps
- One thing to keep in mind is that performance may be an issue when prefixing your stored procedures with sp_. Read this for more information.
- Beginning with SQL Server 2008 you can enforce your database object naming rules by using the Policy-Based Management feature. Here is a good starting point.
- Here you can find useful advice about establishng a naming convention for your stored procedures.
- For a more in-depth covering of the name resolution in SQL 2000 and SQL 2005 see this article "Name resolution, default schema, implicit schema"
About the author
Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
