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 2.0 MB sa 5 Aug 25, 1997 no options set device_fragments size usage free kbytes ------------------- ------------- ---------------- ----------- pubdev 2.0 MB data and log 288 device segment ---------------------- ---------------------- pubdev default pubdev logsegment pubdev system
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 22, “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 23, “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 3.0 MB sa 1 Jan 01, 1900 no options set model 2.0 MB sa 3 Jan 01, 1900 no options set mydata 4.0 MB sa 7 Aug 25, 1997 no options set pubs2 2.0 MB sa 6 Aug 23, 1997 no options set sybsecurity 20.0 MB sa 5 Aug 18, 1997 no options set sybsystemprocs 10.0 MB sa 4 Aug 18, 1997 trunc log on chkpt tempdb 2.0 MB sa 2 Aug 18, 1997 select into/ bulkcopy/pllsort
