Chad Callihan provides solid advice:
Everyone has an opinion on naming stored procedures. One opinion that can be agreed upon is that starting a stored procedure with “sp_” is not the way to go. Using “sp_” can only do harm. Let’s take a look at why that is.
Read on for Chad’s reasoning.
I don’t like prefixes at all for stored procedures (or tables). It’s a common misunderstanding of Hungarian notation, as you don’t provide any new and relevant information in the object name: of course it’s a stored procedure; I’m using “EXEC” to execute it, so what else could it be?
The exception to a very good rule is something which you intend to put in the master database and execute from any database context. The best example of this is sp_whoisactive.