How can I get all user created databases in SQL server 2005?

Question

Hi Guys,

I want list of user created databases in sql server 2005. How can list all user created databases by T-SQL?

Thanks Rajesh

Answer 1

The simplest way is

exec sp_databases

however that will include the system databases, so you'd need to manually exclude them. Not sure if there's a different system sproc that will do it for you.

While you can get more extended info using

select * from sys.databases

I'm quite sure the owner_sid field isn't a reliable indicator of a system database, since I have the same sid on both system and some user databases on my server here.

Answer 2

I'm sure there is a 'better' way, but this works:

SELECT * FROM sys.databases
where owner_sid != 1

Answer 3

SELECT name, owner_sid
FROM sys.databases

owner_sid should provide enough info to distinguish, although I noticed that my Oslo_Repository sid was also specified as 1, so it might not be 100% reliable.

Answer 4

You query sys.databases and remove the system ones. True system databases are master, tempdb, model (ie. db_id 1, ,2 and 3) and mssqlsystemresource. However mssqlsystemresource has some magic behind and does not appear to exists. On the other hand everybody but the SQL engine considers msdb to be a system database too. So the query will be something like:

select * from sys.databases 
where name not in (N'master', N'tempdb', N'model', N'msdb')