User created DBs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

User created DBs

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-01 : 11:10:38

Hi,

How can i get the names of user created databases?

select Distinct (name) from sysobjects where xtype = 'U', i know this statement for tables but is there a statement for Databases?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-01 : 11:17:54

you can get a lists of databases from

select * from master..sysdatabases

But I don't think there is any column that identify it is a user created db in there.

You can refer to Books On Line on the column explaination for sysdatabases

You can probably identify by looking at the crdate column. For SQL Server 2000, system databases, Pubs & Northwind are all with crdate = 2000-08-06

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-02 : 21:44:19

This should do the trick:

select * from master..sysdatabases where sid <> 0x01

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-02 : 21:48:37

quote:
Originally posted by timmy

This should do the trick:
select * from master..sysdatabases where sid <> 0x01

When i run this in my SQL Server 2000, i got several of the user created database excluded from the list.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-02 : 22:25:34

Keep it simple.


select 
	Name
from 
	master..sysdatabases 
where 
	name not in ('master','tempdb','model','msdb','Northwind','pubs')

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-02 : 22:40:27

You might also want to exclude AdentureWorks for SQL Server 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources