Reports information about a particular database or about all databases.
sp_helpdb [dbname [, order]]
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.
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.
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
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)
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)
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
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
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
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 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.
(Cluster environments only) 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.
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. Permission checks do not differ based on the granular permissions settings.
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 |
|
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
