Query to get system databases

  • 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?

  • 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.

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

  • 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

  • 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.

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

  • 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