List all Databases on a Server

Occasionally it is useful to have a record set of all the databases on a server. Thankfully, there is an undocumented system stored procedure that can be used to return just that. The procedure has one required parameter: the command to execute for each database. The procedure also accepts a substitution element as a placeholder for the database name; by default the placeholder is a question mark (?) and can be used as such within the command text.

To demonstrate this stored procedure, execute the following TSQL statement from any connection on your SQL server. It will display the name of each database on the server.

-- Display the name of every database on the server
EXECUTE sp_msforeachdb 'PRINT ''?'';';

This example can easily be extended to create a table and populate it with database names. To do so, create the table in which you would like to store the database names, then replace the PRINT command with an INSERT INTO command referencing the table. To see that usage in action, refer to defragment indices. Also, why not replace the PRINT command with a BACKUP DATABASE command? See it in action here.

Advertisements
Post a comment or leave a trackback: Trackback URL.

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

%d bloggers like this: