sp_helpdb

Description

Reports information about a particular database or about all databases.

Syntax

sp_helpdb [dbname [, order]]

Parameters

dbname: is the name of the database on which to report information. Without this optional parameter, sp_helpdb reports on all databases. dbname can include wildcard characters to return all databases that match the specified pattern.

order: The default order of the output is by lstart, which is the order in which the databases were created or altered. Use device_name along with dbname to display the output of sp_helpdb ordered by device_name.

Examples

Example 1

Displays information about all the databases in Adaptive Server.

sp_helpdb

name          db_size  owner  bid  created       status
------------- -------- ----- ----- -------------- --------------------
master         24.0 MB   sa  1      Jan 07, 2004  mixed log and data
model          8.0 MB    sa  3      Jan 07, 2004  mixed log and data
pubs2          8.0 MB    sa  4      Jan 21, 2004  trunc log on chkpt,
                                                  mixed log and data
sybsystemdb    8.0 MB    sa  31513  Jan 07, 2004  mixed log and data
sybsystemprocs 112.0 MB  sa  31514  Jan 07, 2004  trunc log on chkpt, mixed
                                                  log and data
tempdb          8.0 MB   sa  2      Feb 24, 2004  select into/bulkcopy/ 
                                                  pllsort, trunc log on
                                                  chkpt, mixed log| and 
                                                  data
(1 row affected)
(return status = 0

Example 2

Issued from within pubs2, displays information about the pubs2 database, and includes segment information:

1> use pubs2
2> go
1> sp_helpdb pubs2
2> go

name   db_size  owner  dbid  created        status
------ -------- ------ ----- ------------- -------------------------------
pubs2  20.0 MB   sa     4     Apr 13, 2005  trunc log on chkpt, mixed log 
                                           and data
(1 row affected)
pubs2
device_fragments  size   usage        created            free kbytes
------------------ ------- ------------- ------------------- -------
master             10.0 MB  data and log  Apr 13 2005 10:29AM  2304
pubs_2_dev         10.0 MB  data and log  Apr 13 2005 10:33AM  9888

device         segment
-------------  ----------------------------------------------------
master         default
master         logsegment 
master         system
pubs_2_dev     default
pubs_2_dev     logsegment
pubs_2_dev     system
pubs_2_dev     titleseg1
pubs_2_dev     titleseg2
pubs_2_dev     titleseg3
pubs_2_dev     titleseg4
pubs_2_dev     titleseg5 
return status = 0)

Example 3

Not issued from within pubs2, displays information about the pubs2 database:

sp_helpdb pubs2

name  db_size  owner  dbid    created      status
------------ ------------- ------------------------ ----- ----------------
pubs2   20.0 MB  sa      4     Jan 21, 2004  trunc log on chkpt, single user,
                                            mixed log and data
(1 row affected)device_fragments  size    usage         created              free kbytes
----------------  ----    -----         -------              -------------
master            10.0 MB  data and log  Apr 13 2005  10:29AM  2304
pubs_2_dev        10.0 MB  data and log  Apr 13 2005  10:33AM  9888  
(return status = 0)

Example 4

Specifies device_name for the order parameter to display the device fragments for mydb in alphabetical order, overriding the default sort order of sp_helpdb.

sp_helpdb mydb, device_name

name             db_size  owner  dbid  created      status
---------------  -------  -----  ----  -----------  -----------
mydb             4.5 MB    sa      5     Feb 27, 2003   no options set 

(1 row affected)
device_fragments  size     usage       created                 free kbytes
----------------  -----    ------      --------  ------------------ 
A                 1.5 MB   data only   Feb 27 2003   7:50AM    1530
B                 1.0 MB   log only    Feb 27 2003   7:50AM not applicable
C                 2.0 MB   data only   Feb 27 2003   7:50AM    846

Example 5

Displays the row lock promotion attributes set for the pubtune database:

sp_helpdb pubtune

name    attribute_class  attribute      int_value char_value  comments
----    ---------------  ---------      --------- ----------  ------
pubtune lock strategy   row lock promotion  NULL     PCT = 95, LWM = 300, 
                                                               HWM = 300

Example 6

Displays whether or not a database is a user-created temporary database under the status column:

sp_helpdb "mytempdb3"

name    db_size owner dbid created    status
-------  ------- ----- ---- -------     -----
mytempdb 32.0 MB sa    7    Dec 2, 2001 select into/bulkcopy/pllsort, trunc
                                       log on chkpt, user created temp db

Usage

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.

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 Chapter 3, “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.
In cluster environments – 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 Chapter 4, “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.
sp_helpdb reports if a database is offline.
sp_helpdb reports row lock promotion thresholds, if any are defined for the 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”.

Permissions

Any user can execute sp_helpdb.

Auditing

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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