September 23, 2010

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

4 comments:

Brad Schulz said...

Also:

IF DB_ID(@db_name) IS NOT NULL
print 'Exists!'
ELSE
print 'Does not exist!'

Jeremy said...

I was about to suggest another away and realized Brad Schulz already did it ;)

Anonymous said...

Which of these methods requires the least privileges? I've run into situations where users need admin privileges on the DB to be able to check existence, so I wonder if there is a way to check with less privileges.

Anonymous said...

Here is another way I just solved today:

declare @path sysname
select TOP 1 @path = SUBSTRING(filename, 1, CHARINDEX('\DATA', filename)) FROM master.dbo.sysdatabases where name=DB_NAME();
declare @i int, @file varchar(255)
set @file = @path + 'DATA\' + @DB_NAME + '.mdf'
exec xp_fileexist @file, @i out