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