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.

Table 10.7. 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.