sp_helpdb

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 the SAP ASE 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

Example 7 – Reports the status of database that is being encrypted:

>sp_helpdb
>go
name        db_size    owner dbid  created      durability
    lobcomplvl inrowlen
status
……
test_db     6.0 MB     sa       4  Aug 07, 2013 full
             0 NULL
    encryption in progress: 35%
……

Example 8 – Reports the status of a partially encrypted database:

>sp_helpdb
>go
name        db_size    owner dbid  created      durability
    lobcomplvl inrowlen
status
……
test_db     6.0 MB     sa       4  Aug 07, 2013 full
             0 NULL
    encrypted partly

……

Example 9 – Reports the status of a database that is partially decrypted:

>sp_helpdb
>go
name        db_size    owner dbid  created      durability
    lobcomplvl inrowlen
status
……
test_db     6.0 MB     sa       4  Aug 07, 2013 full
             0 NULL
    decrypted partly

……

Example 10 – Displays information about the durability of a user-created temporary database. For this example, if you create the database:

create temporary database tempdb_explicit on default = 50 
with durability = no_recovery

sp_helpdb displays this output:

sp_helpdb tempdb_explicit
 name db_size owner dbid created durability lobcomplvl inrowlen status 
 ---- ------- ----- ---- ------- ---------- ---------- -------- ------ 
 
 tempdb_explicit  50.0 MB  sa  7    Dec 05, 2012 no_recovery  0  NULL
         select into/bulkcopy/pllsort, trunc log on chkpt, 
         mixed log and data, user-created enhanced performance 
         temp db, allow wide dol rows 

(1 row affected)
device_fragments   size      usage         created          free  kbytes 
----------------- ------ -------------- ------------------ ------------- 
 master          50.0 MB  data and log  Dec 5 2012 8:49PM    49216 
(return status = 0)

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.

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:

Information	Values
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