Tuesday, October 05, 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
Read more: SQL Server curry