Useful System Procedures
Although hundreds of system-stored procedures exist, you will find a core group of 10 or 20 that you commonly use on your system. Many of the procedures prefixed as sp_help are, to risk stating the obvious, helpful. For instance, sp_helpdb returns information about all databases, or if a database name is specified, about a single database, such as sp_helpdb 'pubs'.
Table 10.7 lists some common system-stored procedures.
| Procedure Name | Description |
|---|---|
| sp_help | Lists objects in a database or returns information on a specific object. Can be used to obtain information on any database object. |
| sp_helpdb | Lists databases or returns information on a specific database. |
| sp_helprotect | Displays information on object and statement permissions. |
| sp_who | Returns information on SQL Server connections. |
| sp_lock | Locking information on all, one, or two processes. This is helpful in diagnosing locking and deadlocking problems. |
| sp_configure | Displays or changes global configuration settings. |
| sp_dboption | Displays or changes database configuration settings. |
