CODEWITHSUNDEEP
c#sqldatabaseazureazure-sql-database
DSKVP
DSKVP

Reputation: 663

Checking if database exists or not in SQL Azure

Could any one please tell me how to code whether a database exists or not in sql azure ?

Upvotes: 2

Views: 8526

Answers (3)

MethodMan
MethodMan

Reputation: 18863

Select count(*) from sysobjects where name = 'testdb' returns 0 if not found. put the name of your Database and we will edit the script for you .. all you need to do is copy and paste ok..? here are some additional things you could try as well

Method 1: Use sys.sysdatabases view 

IF EXISTS(SELECT * FROM sys.sysdatabases where name=@testdb)
    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=@testdb)
    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 ''?''='''+@ testdb+''' 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 wherecatalog_name='''+@ testdb+''') print ''the database exists'''
EXEC sp_msforeachdb @sql

Upvotes: 1

sam yi
sam yi

Reputation: 4935

if exists (select * from master.sys.databases where name = '[enter name here]')

Upvotes: 0

David Makogon
David Makogon

Reputation: 71041

Have you tried querying the sys.databases table? That should give you what you're looking for. More info here.

Note: You'll want to run this query against the Master database. Otherwise, you'll only see the name of the current database (and Master).

Upvotes: 6

Related Questions