SQL Server T-SQL User-Defined Function of the Week

Find User PROCs that Start with sp_

Volume 1 Number 43     September 2, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Names matter. Having a naming convention is always a good idea.
I always begin the names of user-defined functions (UDFs) with
"udf_".  Similarly, I begin the names of stored procedures 
with one of several prefixes:

    dba_   For stored procedures in my dba library.
    dp_    For data access procedures that implement the
           CRUD (Create Read Update Delete) operations in the 
           data layer of an object design.
    usp_   For other stored procedures.
    
The prefix that you shouldn't use is "sp_".  That's because
when SQL Server tries to find a stored procedure with the 
sp_ prefix, it looks first in the master database and only after 
the procedure isn't found there does it look in the current
database.  The extra lookup adds unnecessary overhead.

Of course, using sp_ is sort of natural.  Developers are so use
to seeing procedures with that prefix that it's hard to resist.
This week's UDF locates any procedures who's name begins with 
sp_ and returns a table that includes the owner name and
procedure name.  Here's the CREATE FUNCTION script:

/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_Proc_BadPrefixTAB (

)   RETURNS TABLE
    -- NO SCHEMABINDING due to sue of system tables
/* 
* Returns a table of user procedures that use the "sp_" prefix.
* This prefix should be reserved for system procedures.
*
select * FROM dbo.udf_Proc_BadPrefixTAB()
* 
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2003-09-02    ASN     Changed UPPER to LOWER in comparison
* 2003-08-10    ASN     Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Vol 1 #42 9/2/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES
       ROUTINE_SCHEMA as Owner
     , ROUTINE_NAME AS [Name]
     , CREATED 
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'procedure'
       AND LOWER(LEFT(ROUTINE_NAME, 3)) = 'sp_'
    ORDER BY CREATED DESC, ROUTINE_SCHEMA, ROUTINE_NAME
GO

GRANT SELECT ON [dbo].[udf_Proc_BadPrefixTAB] TO [PUBLIC]
GO
\-------Stop copying above this line ---------------------------/

I tried the UDF on my development database and to my surprise 
there were a dozen procedures that begin with sp_.  It seems
that most of them were procedures that I had copied from 
elsewhere for purposes of review, but there were a few that I had
written a long time ago and never renamed.  I guess that it's 
time to either clean out routines that I don't use, or change 
those names.

This query produces the list:
/------- Start copying below this line -------------------------\
select * from udf_Proc_BadPrefixTAB()
go
\-------Stop copying above this line ---------------------------/
(Results)
Owner Name                           CREATED               
----- ------------------------------ ----------------------- 
dbo   sp_FindStringInCode            2003-02-03 21:25:59.420
dbo   sp_ObSize                      2003-01-18 15:17:08.730
dbo   sp_checksize                   2003-01-18 15:05:12.410
dbo   sp_xKillUsers                  2003-01-11 09:08:59.517
dbo   sp_CreateTextFile              2002-12-24 14:42:42.783
dbo   sp_revokepermissions           2002-11-14 11:35:29.010
dbo   sp_grantpermissionstoapprole   2002-11-04 17:23:24.133
dbo   sp_listsysfiles                2002-10-28 17:41:43.107
dbo   sp_grantpermissionsall         2002-10-28 17:38:18.663
dbo   sp_grantpermissionsex          2002-10-28 16:06:17.833
dbo   sp_displayoaerrorinfo          2002-10-12 15:18:54.013
dbo   sp_hexadecimal                 2002-10-12 15:18:53.983
dbo   sp_textcopy                    2002-09-02 09:24:40.023
dbo   sp_decrypt_udf                 2002-08-07 10:56:10.423
dbo   sp_decrypt_sp                  2002-08-07 10:47:59.640

udf_Proc_BadPrefixTAB sorts the newest procedures to the top. 
That makes them easy to spot.  After you've checked your list
once, you only have to take a look at the newest procedures
on the list to see if there's a routine that's worth 
investigation. 

Now that I'm aware that having procedures with these names is
more common than I thought, I'll add a report that checks this
every day to my daily routine.

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+