A system stored procedure is a precompiled collection of Transact-SQL statements. Many system stored procedures are available for managing SQL Server and for displaying information about databases and users.
The names of most system stored procedures begin with sp_. System stored procedures are located in the master database and are owned by the system administrator, but many of them can be run from any database. If a system stored procedure is executed in a database other than master, it operates on the system tables in the database from which it is executed.
You can also write your own precompiled collection of Transact-SQL statements, called stored procedures, that can be executed from any database.
In addition to system stored procedures, extended stored procedures are installed with SQL Server. Extended stored procedures provide additional functionality for SQL Server by providing a way to dynamically load and execute a function within a dynamic-link library (DLL) in a manner similar to that of a stored procedure, seamlessly extending SQL Server functionality. Actions outside of SQL Server can be easily triggered and external information returned to SQL Server. Return status codes and output parameters (identical to their counterparts in regular stored procedures) are also supported.
SQL Server includes system stored procedures that add and drop extended stored procedures and that provide information about extended stored procedures. In addition, SQL Server provides a number of extended stored procedures. Other extended stored procedures can be created by programmers using Microsoft Open Data Services. For information about creating extended stored procedures, see Microsoft SQL Server Programming Open Data Services.
For information about system stored procedures, stored procedures, and extended stored procedures, see the Microsoft SQL Server Transact-SQL Reference.