Reports information about a particular database or about all databases. Displays a column for Asynchronous Log Service (ALS).
sp_helpdb [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.
Displays information about all the databases in Adaptive Server:
sp_helpdb
name db_size owner dbid created status -------------- -------- ----- ---- -------------- ------------------ master 5.0 MB sa 1 Jan 01, 1900 no options set model 2.0 MB sa 3 Jan 01, 1900 no options set pubs2 2.0 MB sa 6 Sep 20, 1995 no options set sybsystemprocs 16.0 MB sa 4 Sep 20, 1995 trunc log on chkp tempdb 2.0 MB sa 2 Sep 20, 1995 select into/bulkcopy
Issued from within pubs2, displays information about the pubs2 database, and includes segment information:
sp_helpdb pubs2
name db_size owner dbid created status ----- ------- ----- ---- ------------ ---------------------- pubs2 2.0 MB sa 4 Mar 05, 1993 abort tran when log full
device_fragments size usage free kbytes ----------------- ------ ------------ ------------- master 2.0 MB data and log 576
device segment ------------------------------ ------------------------------ master default master logsegment master system
name attribute_class attribute int_value char_value comments ------- --------------- ------------- --------- ----------- -------- pubs2 buffer manager cache binding 1 pubs2_cache NULL
Not issued from within pubs2, displays information about the pubs2 database:
sp_helpdb pubs2
name db_size owner dbid created status ----- ------- ----- ---- ------------ ---------------------- pubs2 2.0 MB sa 4 Mar 05, 1993 abort tran when log full
device_fragments size usage free kbytes ----------------- ------ ------------ ------------- master 2.0 MB data and log 576
name attribute_class attribute int_value char_value comments ------- --------------- ------------- --------- ----------- -------- pubs2 buffer manager cache binding 1 pubs2_cache NULL
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
Shows the advanced log service column in the output for sp_helpdb.
sp_helpdb "mydb"
------------ name db_size owner dbid created status -------------------------------------------------- mydb 3.0MB sa 2 June 09, 2002 trunc log on chkpt attribute --------- async log serv
See Chapter 2, “Advanced Optmizing Tools” in Performance and Tuning: Optimizer guide for more information about advanced log service.
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 12, 2001 select into/bulkcopy/pllsort, trunc log on chkpt, user created temp db
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.
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 repor. sp_helpdb also includes a column titled free pages, which is the value for the number of free pages the log segment has.
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”.
Any user can execute sp_helpdb.
Commands alter database, create database
System procedures sp_configure, sp_dboption, sp_rename
