How to get all databases on a SQL Server database

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
http://msdn.microsoft.com/en-us/library/ms176070.aspx

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’ ]

Data returned:

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.
http://msdn.microsoft.com/en-us/library/ms178568.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s