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

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:

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

See also

Documents Chapter 3, “Advanced Optmizing Tools” in Performance and Tuning: Optimizer, Chapter 4, “Expressions, Identifiers, and Wildcard Characters” in Reference Manual: Building Blocks,

Commands alter database, create database

System procedures sp_configure, sp_dboption, sp_rename