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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "Check if Database Exists In SQL Server – Different ways"
  1. Brad Schulz said...
    September 28, 2010 at 5:25 PM

    Also:

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

  2. Jeremy said...
    September 28, 2010 at 10:53 PM

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

  3. Anonymous said...
    September 29, 2010 at 5:31 AM

    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.

  4. Anonymous said...
    September 29, 2010 at 1:11 PM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions