Here is a very nice stored procedure to use when you don’t know (or remember) the databases on the server you are working on.
sp_databases will return all the database names as well as their size (in kilobytes) and any remarks for the Database Engine, this field will always return NULL.
You can use the database names returned as parameters in the “USE” statement to change from database to database depending on the tasks being performed.
Permissions required are CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission. You must also have access to the database being returned.
More information can be found here
An example of executing sp_databases.
Another very nice stored procedure you can use is sp_helpdb.
You can use this to get information on all databases on the server, by leaving the database “name” off,
or passing the database name to the procedure like so.
sp_helpdb [ [@dbname= ] ‘name’ ]
name: database name
db_size: total size of database in megabytes
owner: database owner
dbid: database ID
created: date the database was created
status: this is a comman delimited list of values for the database options currently set
compatibility_level: database compatibility level: 60,65,70,80,90, or 100.
An example of executing sp_helpdb without a database name passed in.
An example of executing sp_helpdb with a database name passed in.
Notice the additional information returned when specifying a database?
name: logical filename
fileid: file ID
filename: operating-system file name (physical file name)
filegroup: filegroup in which the file belongs
size: file size in megabytes
maxsize: maximum size the file can grow. A value of UNLIMITED in this field indicates the file grows until the disc is full
growth: growth increment of the file. This indicates the amount of space added to the file each time the new space is needed
usage: usage of the file. For the data file, the value is the “data only” and for the log file the value is the “log only”
More information can be found here.