A user procedure named sp_ReportSales can work for years and still be a bad bet. The sp_ prefix tells SQL Server to treat the name like a system procedure, changing lookup behavior and risking a future collision. A short inventory shows where that bet lives today.
How the sp_ Prefix Changes Name Lookup
System stored procedures use the sp_ prefix. SQL Server checks system procedure names as part of resolving a call that starts that way. If an application calls a user procedure without a schema, a matching system name can take precedence. Even without a collision, the special name-resolution path can add a small cache miss or lookup cost. The cost is rarely the largest problem in a slow application, but it buys nothing useful.
I treat the prefix as a naming defect rather than a tuning emergency. A procedure that runs for minutes will not become fast because its first three characters changed. A procedure called thousands of times can make small overhead easier to notice. The larger risk is correctness: a new system procedure with the same name can change what an unqualified call means. Do your callers include the schema?
Inventory the sp_ Prefix Across the Instance
A database query against sys.procedures finds local names. An instance-wide inventory must visit each accessible online database. The script below loops over databases, quotes each database identifier, and collects the results in one temporary table. Run it with an account allowed to inspect the databases you intend to review. A database the login cannot access will not appear. The is_ms_shipped filter drops Microsoft's own sp_ procedures in master and msdb, which would otherwise bury your results.
SET NOCOUNT ON;
CREATE TABLE #SpPrefix
(
database_name sysname,
schema_name sysname,
procedure_name sysname
);
DECLARE @db sysname, @sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state_desc = N'ONLINE' AND HAS_DBACCESS(name) = 1;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'INSERT INTO #SpPrefix
SELECT @dbname, s.name, p.name
FROM ' + QUOTENAME(@db) + N'.sys.procedures AS p
JOIN ' + QUOTENAME(@db) + N'.sys.schemas AS s
ON s.schema_id = p.schema_id
WHERE p.name LIKE N''sp[_]%''
AND p.is_ms_shipped = 0;';
EXEC sys.sp_executesql @sql, N'@dbname sysname', @dbname = @db;
FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs;
DEALLOCATE dbs;
SELECT database_name, schema_name, procedure_name
FROM #SpPrefix
ORDER BY database_name, schema_name, procedure_name;
DROP TABLE #SpPrefix;This is an inventory script, not a rename script. Review the results and distinguish your procedures from objects supplied by a vendor. A vendor package can depend on its names, so rename work there starts with support guidance. The presence of sp_ alone does not authorize a production change.
Find the Actual Callers
Search application code, jobs, dynamic SQL, and other modules for each old name. Dependencies recorded by SQL Server are useful but incomplete when a name is assembled into a string. Include calls from reporting tools and scheduled tasks. Capture whether each caller uses EXEC dbo.sp_ReportSales, EXEC sp_ReportSales, or a three-part name. The unqualified form deserves special attention.
I make one test call through each real path before changing anything. If the procedure has permission grants, ownership chaining, or an EXECUTE AS clause, record those too. A replacement with the same body but different security can fail after deployment. The rename plan should prove behavior, not only that the text compiles.
Put the New Name in Place First
Choose a descriptive name without the reserved prefix, for example dbo.ReportSales. Create the new procedure from the reviewed definition, including its parameters and security context. Test it directly and grant the required permissions. Do not use sp_rename as a substitute for updating the module definition and dependencies. A clean definition under the new name makes the deployment easier to inspect later.
At cutover, remove the old procedure and create a synonym with the old name pointing to the new procedure. A synonym can target a stored procedure. Callers using the old name can continue while they are updated in stages. The two objects cannot occupy the same schema and name at the same time, so plan that brief switch as a controlled deployment. The code shows the final bridge after the new procedure exists.
DROP PROCEDURE dbo.sp_ReportSales;
GO
CREATE SYNONYM dbo.sp_ReportSales FOR dbo.ReportSales;
GO
EXEC dbo.sp_ReportSales;Do not paste that block against an unreviewed production object. Replace the names and confirm the new procedure has the same contract before dropping the old one. The synonym is a bridge, not the destination. Calls to the old sp_ name still carry the naming concern until the application uses the new name. Test the old and new paths, then remove the synonym after the last caller changes.
Check Security and Deployment Order
A synonym does not store the procedure body or copy its permissions. Test execution under the application login, not only under a privileged DBA login. Include a rollback plan: retain the old definition and grants so the deployment can be reversed if a caller fails. Check jobs and application logs immediately after cutover. A successful CREATE SYNONYM says little about a report that runs only on Friday.
If several procedures share the prefix, migrate them in small groups. That makes a missing caller easier to locate. Record the old and new names together in the deployment note, along with the synonym removal date. A bridge that never comes down becomes a second permanent naming system.
After creating the synonym, call it with the same application login and parameters used by production. Confirm the result, output parameters, and error behavior, not just that EXEC returns without a syntax error. Synonyms do not make invalid targets valid, and permission checks still matter at execution. If a procedure is called from dynamic SQL, test that path separately. The database context used to resolve an unqualified name can differ from the one in your manual test. Keep the old-name bridge only for the period needed to change those callers, then remove it in a reviewed release.
Verify the Inventory Again
Run the inventory after each rollout and separate remaining procedures from transitional synonyms. sys.procedures will no longer list the renamed object under sp_; sys.synonyms will show a remaining bridge. Confirm new calls use a schema-qualified name and that no unexpected system procedure is being reached. Watch real executions or application tests rather than trusting a source search alone.
A new naming rule can prevent the defect from returning. Put it in code review or a database project check, and explain the reason. The goal is a predictable call target, not a prettier alphabetic list in Object Explorer.
Related reading on this blog: Easiest Way to Copy All Stored Procedure Definitions and Last Used Stored Procedure.
The sp_ prefix is not a helpful shortcut, it is a name-resolution risk with an easy escape route.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
