To find the names of the database devices on which a particular database resides, use sp_helpdb with the database name:
sp_helpdb pubs2
name db_size owner dbid created status --------- ---------- --------- ---- -------------- -------------- pubs2 20.0 MB sa 4 Apr 25, 2005 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data device_fragments size usage created free kbytes ------------------- ------------- ------------- ---------- ------------ master 10.0MB data and log Apr 13 2005 1792
pubs_2_dev 10.0MB data and log Apr 13 2005 9888 device segment ---------------------- ---------------------- master default master logsegment master system pubs_2_dev default pubs_2_dev logsegment pubs_2_dev system pubs_2_dev seg1 pubs_2_dev seg2
sp_helpdb reports on the size and usage of the devices used by the named database. The status column lists the database options. These options are described in Chapter 8, “Setting Database Options.”
If you are using the named database, sp_helpdb also reports on the segments in the database and the devices named by the segments. See Chapter 8, “Creating and Using Segments,” for more information.
When you use sp_helpdb without arguments, it reports information about all databases in Adaptive Server:
sp_helpdb
name db_size owner dbid created status ------------- -------- ----- ---- ------------ ------------------- master 48.0 MB sa 1 Apr 12, 2005 mixed log and data model 8.0 MB sa 3 Apr 12, 2005 mixed log and data pubs2 20.0 MB sa 6 Apr 12, 2005 select into/ bulkcopy/pllsort, trunc log on chkpt, mixed log and data sybsystemdb 8.0 MB sa 5 Apr 12, 2005 mixed log and data sybsystemprocs 112.0 MB sa 4 Apr 12, 2005 trunc log on chkpt, mixed log and data tempdb 8.0 MB sa 2 Apr 12, 2005 select into/ bulkcopy/pllsort, trunc log on chkpt, mixed log and data
