Sql-server – How to get a list of all of the user databases via t-sql

sql server

I want to get a list of all of the user databases from an mssql server instance. What's the best way to do this?

I know I can select from sys.databases, but I don't see any way to filter out system databases besides hardcoding a list of names to exclude.

I need the script to work on 2000/2005 and 2008.

If the approach I listed above is the only way to go, what are list of names I should exclude? I don't know if 2005 or 2008 added any new system databases off the top of my head.

Best Answer

Was looking in to this again today and decided to profile what Management Studio was doing to populate the Object Explorer details.

Object Explorer > System Databases

Turns out the solution Microsoft have implemented is pretty simplistic and boils down to the following:

SELECT *
FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

Please note that this was performed using SSMS 2008R2 (10.50.4033.0).

Related Topic