Andre Loker .NET wisdom

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 :-)