Get the names of databases on a SQL Server

June 20, 2008 at 6:34 PMAndre Loker

For administrative tasks you might need the names of all databases on a given SQL Server. Luckily SQL Server comes with some neat stored procedures that help a lot, for example:

   1: EXEC sp_databases; -- get name, size and remarks
   2: EXEC sp_helpdb;    -- get name, size, owner, dbid, creation date, 
   3:                    -- status and compatibility level

Those two SPs are certainly nice to have, but they return more than you might need. Given that it is not so easy to perform a SELECT on the results here's a simple query to return the names of all (online) databases:

   1: SELECT db_name(database_id) 
   2: FROM sys.master_files
   3: WHERE state = 0 -- only fetch databases that are online
   4: GROUP BY database_id;

Admittedly I did not come up with this all by myself. I simply looked at what sp_databases does and extracted the stuff I needed :-)

Posted in: Databases | Snippets

Tags: ,

Pingbacks and trackbacks (1)+