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

1 comment:

  1. Recover .sql databases can fulfil repair sql server database. Utility repairs after viruses, hard drive failures, unstable software. It uses modern, clear recovery wizard is helpful for novices and experts.

    ReplyDelete