Check if Database Exists In SQL Server – Different ways

A very frequently asked question is how to to check if a Database exists in SQL Server. Here are some different ways.

The following code will be common for all the methods:

DECLARE @db_name varchar(100)
SET @db_name='master'

Method 1: Use sys.sysdatabases view

IF EXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)
PRINT 'The database exists'
else
PRINT 'The database does not exist'

Method 2: Use sysdatabases system table from master database

IF EXISTS(SELECT * FROM master..sysdatabases WHERE name=@db_name)
PRINT 'The database exists'
else
print 'The database does not exist'

Method 3: Using of sp_msforeachdb

--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)
SET @sql='if ''?''='''+@db_name+''' print ''the database exists'''
EXEC sp_msforeachdb @sql 

Method 4: Using sp_msforeachdb with information_schema.schemata

--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)
SET @sql='if exists(select * from ?.information_schema.schemata where
catalog_name='''+@db_name+''') print ''the database exists'''
EXEC sp_msforeachdb @sql