Re: using 'sp_' as a naming convention for stored procedures
From: Tim S (stahta01_at_juno.com)
Date: 03/15/04
- Next message: Max Spectrum: "Re: Keylocks and updates"
- Previous message: Grant Case: "Re: Printing the KEYS of all tables"
- In reply to: Tom Moreau: "Re: using 'sp_' as a naming convention for stored procedures"
- Next in thread: Aaron Bertrand [MVP]: "Re: using 'sp_' as a naming convention for stored procedures"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 14 Mar 2004 21:04:21 -0500
>From Best Practices Analyzer Tool for Microsoft SQL Server 2000 beta version
1.00
Rule: User Object Naming
Category
Database Design
Description
This rule checks for user objects with any of the following prefixes: sp_,
xp_, fn_, and sys. Though this practice is supported, it is recommended that
the prefixes not be used to avoid name clashes with Microsoft shipped
objects:
sp_: this prefix should not be used for user defined stored procedures
xp_: this prefix should not be used for user defined extended stored
procedures
fn_: this prefix should not be used for user defined scalar functions
sys: this prefix should not be used for user defined tables and views
Tim S
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:e8K5U0hCEHA.3804@TK2MSFTNGP09.phx.gbl...
>From the BOL "Creating a Stored Procedure":
System Stored Procedures
Many of your administrative activities in Microsoft® SQL ServerT 2000 are
performed through a special kind of procedure known as a system stored
procedure. System stored procedures are created and stored in the master
database and have the sp_ prefix. System stored procedures can be executed
from any database without having to qualify the stored procedure name fully
using the database name master.
It is strongly recommended that you do not create any stored procedures
using sp_ as a prefix. 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.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql . "Scott" <anonymous@discussions.microsoft.com> wrote in message news:cda301c40a19$103893e0$a301280a@phx.gbl... I know that using 'sp_' as a naming convention for stored procedures is not a good idea because MS uses that naming for system stored procedures. I am working on a system where the original developer had all of the stored procedures named "sp_" then the name. I am being told that this is causing a problem with the server because of it. The DBA group told me that when using "sp_" as a name for a stored procedure it automatically causes the call to go to the master DB to look it up. I have never heard of this before. Has anyone else? Additional information: In my connection string I specify the DB name. I do not reference any tables outside of the DB the sproc resides in. Any feedback would be appreciated. thanks Scott http://support.microsoft.com/default.aspx?scid=kb;en- us;159901 The "Transact-SQL Reference," under the User-defined System Stored Procedures heading, states: When executed, a stored procedure is typically found within the current database. If the procedure name begins with sp_ and is not found in the current database, SQL Server will look in the master database. This statement incorrectly implies that you can create user-defined system stored procedures outside of the master database. The statement in Example H of the CREATE PROCEDURE Examples section is correct: "user-defined system stored procedures...must be created by the system administrator in the master database."
- Next message: Max Spectrum: "Re: Keylocks and updates"
- Previous message: Grant Case: "Re: Printing the KEYS of all tables"
- In reply to: Tom Moreau: "Re: using 'sp_' as a naming convention for stored procedures"
- Next in thread: Aaron Bertrand [MVP]: "Re: using 'sp_' as a naming convention for stored procedures"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
