List SQL databases in SQL Server — Parv The IT Geek

Have you ever wanted to list out all the databases you have on a server? Well I am going to show you how to do it quickly and easily.

The other day I had completed a migration from a creaky old dinosaur server to a brand new powerful server which always brings a smile on my face as I like new stuff. I stopped all jobs, put the databases into offline, updated my documentation and diarised a week in the future to finish my clean-up work.

The next day my colleague asked me where I had moved the databases as they did not appear to still be on the server. When he listed the databases using the two following procedures it showed that we were 8 databases down which was the same amount I had put into offline mode.

EXEC sp_databases
 EXEC sp_helpdb

I took a step back and did a quick manual search on the server which when you have a few thousand databases can take a while. I found all eight databases and came to the realisation that those two stored procedures can’t find offline databases.

I used the below query to get an accurate list of the databases.

SELECT name
 FROM sys.sysdatabases


Originally published at https://parvtheitgeek.com on February 12, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

<span>%d</span> bloggers like this: