User-defined System Stored Procedures

During installation, numerous system stored procedures are provided for convenience as shortcuts to retrieve or manipulate information stored in the system tables. Most SQL Server database administration tasks are accomplished with these predefined system procedures; however, you can also create user-defined system stored procedures. By default, each frequently used system stored procedure name begins with the sp_ prefix. To create a user-defined system stored procedure, use sp_ for the beginning of the procedure_name. Once created, this procedure will have the following characteristics:

• It can be executed in any database.
• When executed, a stored procedure is typically found within the current database. If the procedure name begins with sp_ and is not found in the current database, SQL Server will look in the master database. Tables referenced within a stored procedure named with sp_ that are not resolved within the current database will be looked up in the master database.
• Objects referenced within a stored procedure are resolved when the procedure is executed, not when it is created.