Versatile Sp_

The ability to use the sp_ prefix to identify views and procedures in the master database that have a special property is interesting. Many people think that sp stands for system procedure. It actually stands for special object, meaning that an object named with the sp_ prefix has some special capabilities, such as the ability to be referenced from any database without requiring qualification by the owner name or database name. You can even use sp_ to create tables that you can access from any database. Because tables don't reference other objects, you might not be concerned that a table with the sp_ prefix can refer only to objects in the database it's created in. However, consider the table that the code in Listing A creates. You can insert into and select from this table from any database, but the default value db_name() will always reference the master database.

At times, you might want to be able to reference a view or table from any database without having to use the fully qualified name. If you don't need to reference your current database, you might find the trick of using sp_ to name objects other than procedures useful. However, if you're used to thinking that sp means system procedure instead of special object, you might find it hard to use this naming convention to refer to a view or table.