sp_helpdb

Reports information about a particular database or about all databases.

Syntax

sp_helpdb [dbname [, order]]

Parameters

Examples

Usage

There are additional considerations when using sp_helpdb:
  • When you run sp_helpdb on a fully encrypted database, it reports its encryption status:
    • Encrypted
    • Encryption in progress
    • Decryption in progress
    If the database is being encrypted or decrypted, sp_helpdb reports the percentage of work that has completed.
  • sp_helpdb reports on the specified database when dbname is given. If no value is supplied for dbname, sp_helpdb reports on all the databases listed in master.dbo.sysdatabases.

  • sp_helpdb reports all database-specific properties and settings, such as: whether a database is offline, compression type, large object compression level, in-row large object length, row lock promotion thresholds (if any are defined for the database), and so on.

  • If you enable asynchronous log service on a database, the attribute column in the sp_helpdb output displays “async log srv”.

    For more information about asynchronous log service, see sp_dboption, and Advanced Optmizing Tools in Performance and Tuning: Optimizer.

  • For log segment disk pieces in a dedicated log database, sp_helpdb issues “not applicable” for the free space field in its per-disk-piece report. sp_helpdb also includes a column titled free pages, which is the value for the number of free pages the log segment has.

  • (Cluster Edition) sp_helpdb does not display device-related information if the specified database is a local temporary database owned by a remote instance.

  • dbname can include wildcard characters to return all databases that match the specified pattern. See Expressions, Identifiers, and Wildcard Characters in Reference Manual: Building Blocks for details about using wildcard characters.

  • Executing sp_helpdb dbname from dbname includes free space and segment information in the report.

  • sp_helpdb displays information about a database’s attributes, giving the attribute’s class, name, integer value, character value, and comments, if any attributes are defined. Example 3 shows cache binding attributes for the pubs2 database.

  • A database created with the for load option has a status of “don’t recover” in the output from sp_helpdb.

  • When Component Integration Services is enabled, sp_helpdb lists the default storage location for the specified database or all databases. If there is no default storage location, the display indicates “NULL”.

  • The status column of sp_helpdb includes these descriptions for database durability:
    • user created temp db – normal temporary database created by the user (that is, created without specifying the durability parameter).
    • user-created enhanced performance temp db – user-created temporary database created explicitly with the no_durability parameter. Because a database created with no_durability depends on licensing, it may not come online if the license expires.
See also:
  • Advanced Optmizing Tools in Performance and Tuning: Optimizer, Expressions, Identifiers, and Wildcard Characters in Reference Manual: Building Blocks.

  • alter database, create database in Reference Manual: Commands

Permissions

Any user can execute sp_helpdb. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_configure
sp_dboption
sp_rename