The importance of the sp_ prefix
Using the “sp_” prefix for a stored procedure has some interesting effects on SQL Server. Firstly it looks for the stored procedure in the Master database (dbo schema) and then it looks in the current database (default schema for the user, then dbo schema).
The reason it is looking in Master first is that it assumes that any stored procedure starting sp_ is a system procedure, provided by Microsoft. The system procedures have some interesting attributes that can make them very useful:
- The execution of dynamic SQL will have the context of the database that the stored procedure is being run from.
- The execution of static SQL will have the context of the Master database
When the system procedures are created during setup, an undocumented stored procedure is used to flag them as system procedures:
- sp_MS_marksystemobject
This procedure changes the behaviour of the static SQL within a procedure so that it too executes in the context of the database from which it is being executed.
As always with undocumented stored procedures, Microsoft does not support their use and reserves the right to change or remove them without notice so they are to be used with extreme caution.
I have a suite of stored procedures that I create in the master database and flag as system procedures so that they can be used in the scripts that I use to make changes to database objects. Having them in a central location and accessible from all databases within an instance makes these procedures very useful.
The procedures that I have created are mostly used to remove objects that may prevent the dropping of an existing object, e.g. to remove foreign keys that reference a primary key or removing stored procedures that reference a table type.
Common Factors
All of my system procedures are prefixed sp_dba_ so that they are easily identifiable.
They all have an input parameter @aSPMode which defaults to ‘0’ indicating that no database objects should be dropped, instead details of them should be returned.
All of the table related procedures have an input parameter @aTableName that accepts the name of a table. The table type procedure has an input parameter @aTableTypeName that accepts the name of the table type.
Some of the procedures have an input parameter @aDirection that is either ‘in’ or ‘out’ indicating the direction of reference of the objects, e.g. all foreign keys referencing a table is ‘in’ and all foreign keys stemming from a table is ‘out’.
sp_dba_Table_DropFK
This procedure is used to drop foreign keys referencing an object associated with a table. The objects include that may be referenced include primary keys and unique constraints.
sp_dba_Table_DropPK
This procedure is used to drop all foreign keys referencing a primary key for a given table.
sp_dba_Table_DropUC
This procedure is used to drop all foreign keys referencing the unique constraint for a given table.
sp_dba_TableType_DropRef
This procedure drops all stored procedures that reference the table type.
Conclusion
These procedures form the basis of a larger suite of tools that I have in the form of system procedures. These ones however are the procedures that I call in the scripts that I use to build database objects.