Experimenting with the 'sp_' thing...

Here is what I tried...

Created two SPs in the master db...one with a prefix 'sp_', another just a name like

create proc sp_a as select 'a1'
create proc b as select 'b1'

Both compiled in master db of SQL 2005 server. Needless to say it will work fine when called from master.

I tried caling them from a user db.

exec sp_a
exec b

It could only executed sp_a and threw Could not find stored procedure 'b'

The upshot is it only looks up the SPs prefixed with 'sp_' in the master.

Now a big surprise.... I executed

drop proc sp_a

from the user db and it WORKED!

Obviously it couldnt locate 'b' for dropping as it was looking in the current db only.

Next, I created the two procs only in the user db and though both worked within the user db, both failed from master db.

Then I ran in the master db

create proc sp_a as select 'a1'
create proc b as select 'b1'


and at the user db

create proc sp_a as select 'a2'
create proc b as select 'b2'


First I executed both at master db, result

a1
b1

Then at user db,

a2
b2

Well, it does look for the sp with prefix 'sp_' in the calling db first.


Now the funny part, I could actually run the statement drop proc sp_a
twice at the user db, first it drops the one at the user and then at the master.

To sum up, the procs with 'sp_' prefix are first looked into the current db and if not found then searched in the master db.