March 23, 2013 at 5:29 am
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?
March 23, 2013 at 6:01 am
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.
---------------------------------------------------------------------
March 23, 2013 at 9:37 am
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[/url]
Learn Extended Events
March 23, 2013 at 11:19 am
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.
---------------------------------------------------------------------
April 12, 2013 at 7:24 am
Thanks for replies
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply