Search

Wednesday, January 12, 2011

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'elsePRINT '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'elseprint 'The database does not exist'

Method 3: Using of sp_msforeachdb
--If you dont get a message, the database doesn't existDECLARE @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 existDECLARE @sql varchar(1000)SET @sql='if exists(select * from ?.information_schema.schemata wherecatalog_name='''+@db_name+''') print ''the database exists'''EXEC sp_msforeachdb @sql

No comments:

Post a Comment