SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to get system databases


Query to get system databases

Author
Message
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 1798
Hi,
I want a query to get list of databases except system databases from sys.databases.I don't know how to differentiate system databases from user databases.Is there any way to do it?
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 13687
the system databases are always database ID (dbid) 1 -4, so use where clause

where database_id > 4

these will always be user databases.

If you have reporting services this will include the SSRS databases, chances are these are dbid 5 and 6.

---------------------------------------------------------------------
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31820 Visits: 18550

SELECT name
FROM sys.databases
WHERE database_id <= 4
/*
Report Services where collation has not been changed from default
Inclusion of SSRS dbs is questionable as system databases
*/
OR (name LIKE '%ReportServer%'
AND collation_name = 'Latin1_General_CI_AS_KS_WS')
/*
Catch any distribution databases
*/
OR is_distributor = 1
/*
This does't account for database_id 32767
32767 Will appear in some queries and is the mssqlsystemresource
hidden db.
*/





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 13687
Jasons query returns the databases that ARE system databases so you would have to flip the logic.

I THINK SQL2005 includes the resource database in the query but SQL2008 and up don't, but I may have that the wrong way round :-).

You'll have to test that.

---------------------------------------------------------------------
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 1798
Thanks for replies
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search