Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to get system databases Expand / Collapse
Author
Message
Posted Saturday, March 23, 2013 5:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:11 AM
Points: 272, Visits: 1,756
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?
Post #1434588
Posted Saturday, March 23, 2013 6:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 5,888, Visits: 13,060
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.


---------------------------------------------------------------------

Post #1434594
Posted Saturday, March 23, 2013 9:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 18,055, Visits: 16,086
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
Post #1434622
Posted Saturday, March 23, 2013 11:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 5,888, Visits: 13,060
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.


---------------------------------------------------------------------

Post #1434623
Posted Friday, April 12, 2013 7:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:11 AM
Points: 272, Visits: 1,756
Thanks for replies
Post #1441687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse