How can I get a list of all of the user databases via t-sql?

Question

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.

Answer 1

Not sure if you can offhand. One note -- on 2k you'll have to use master.dbo.sysdatabases and not master.sys.databases (which doesn't exist in 2k).

Answer 2

As nasty as it sounds to hardcode things. The names and number of system databases has been fairly consistent for several versions of SQL. However, if that is too unpleasant you could semi-hardcode them into a table and then plug that into your query.

Answer 3

select * from INFORMATION_SCHEMA.SCHEMATA 
where catalog_name not in('master', 'tempdb', 'msdb','model','Resource')

Information_Schema should be your first thought when thinking about sql that references system objects and needs to work with more than one version of SQL Server. Other solutions include SQL-SMO (not a t-sql solution, but is cross version compatible)

Answer 4

This works in 2005, not 100% sure about the other versions but I think it will fly. It's a bit of a hack but might get you what you need:

 select * from sys.databases where len(owner_sid)>1