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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan