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.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features