Getting help on databases

sp_helpdb can report information about a specified database or about all Adaptive Server databases.

sp_helpdb [dbname] 

This example displays a report on pubs2 on a server using a page size of 8K.

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
pub­s_2_dev             system
pubs­_2_dev             seg1
pubs­_2_dev             seg2

sp_databases lists all the databases on a server. For example:

sp_databases
database_name     database_size  remarks
----------------- -------------  ------------
master                     5120  NULL 
model                      2048  NULL
pubs2                      2048  NULL 
pubs3                      2048  NULL
sybsecurity                5120  NULL
sybsystemprocs            30720  NULL
tempdb                     2048  NULL
 
(7 rows affected, return status = 0)

To find out who owns a database, use sp_helpuser:

sp_helpuser dbo
Users_name    ID_in_db Group_name   Login_name
------------- -------- ------------ ------------
dbo                  1 public       sa

To identify the current database, use db_id and db_name:

select db_name(), db_id()
------------------------------ ------
master                              1