Tuesday, 1 April 2014

Check if Database Exists In SQL Server – Different ways

Hi Friends,in this article i will explain about Check if Database Exists In SQL Server – Different ways.
I already explained in the previous articles about How to Get List of Stored Procedures based on table name search, using sql server 2008?,Differences and Similarities Between LEN and DATALENGTH Functions in SQL Server and Common Table Expressions(CTE) in SQL SERVER 2008

Way 1:

IF  NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MASTER')
PRINT 'Not Exists'
ELSEPRINT 'Exists'



Way 2:

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='MASTER')
PRINT 'Not Exists'
ELSE
PRINT 'Exists'

Way 3:
--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000),@db_name varchar(1000)
set @db_name='master'
SET @sql='if exists(select * from ?.information_schema.schemata where
catalog_name='''+@db_name+''') print ''the database exists'''
EXEC sp_msforeachdb @sql

Way 4:
IF DB_ID('MASTER') IS NULL
PRINT 'Not Exists'
ELSE
PRINT 'Exists'

Way 5:
DECLARE @sql varchar(1000),@db_name varchar(1000)
set @db_name='mastesaasar'
SET @sql='if ''?''='''+@db_name+''' print ''the database exists'' else print ''The Database Not Exists'''
EXEC sp_msforeachdb @sql

Way 6:
DECLARE @DB sysname = 'MASTERNew'
BEGIN TRY
  EXEC sp_helpdb @DB
END TRY
BEGIN CATCH
  PRINT 'Not Exists'
END CATCH



No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.