System Stored Procedures

28 Dec 2010 3-minute read Al Eardley
Data Platform
SQL ServerT-SQL

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:

  1. The execution of dynamic SQL will have the context of the database that the stored procedure is being run from.
  2. 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.

Table of Contents


Comment on this post: