CODEWITHSUNDEEP
sql-server
Anuj Thite
Anuj Thite

Reputation: 75

Get list of all System Databases in SQL Server

I know how to fetch list of all databases present on particular Server.

SQL Query:

SELECT *
FROM master.sys.databases
EXEC sp_databases

But, it gives me all database's like System as well as User Defined Databases.

I want to know how get a list of User Defined/System Databases only present in SQL Server as I am not able to see any column present in master.sys.databases, using which I can filter it to achieve my requirement.

Upvotes: 3

Views: 1838

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46415

The sys.databases catalog view does not provide a column to distinguish between user and system databases. However, the names are well-known as listed in the documentation which may be used to exclude them as needed. Additionally, you may want to exclude the SSISDB catalog and distributor database, if present. Below is an example query.

SELECT name
FROM sys.databases
WHERE
    name NOT IN(N'master',N'model',N'tempdb',N'msdb',N'SSISDB')
    AND is_distributor = 0;

BTW, this is a snippet of the query SSMS uses to enumerate system databases as gleaned from a trace:

WHERE
(CAST(case when dtb.name in (''master'',''model'',''msdb'',''tempdb'') then 1 else dtb.is_distributor end AS bit)=@_msparam_0)

Upvotes: 8

Related Questions