When sp_ doesn’t mean “stored procedure”

For some people in the community this seems to be very common knowledge. Well, if it’s so then shame on me, because I didn’t know…

I have been asked some times how those special system stored procs like sp_help work. Those sprocs that only exist in master database, but still you can call them from every DB you are in. So far I thought this was something special, internal, of SQL Server… Well, wrong…

The trick is easy: Whatever object you create in master database that is called sp_<something> you can automatically use in every DB on that server. Because in this case sp_ doesn’t mean “stored procedure” but it means “special”…

Thanks at this point to Kelan Delaney who brought this up at a presentation today…