How to check if a database exists in SQL Server?

ID : 10150

viewed : 28

Tags : sql-serverdatabasetsqlsql-server

Top 5 Answer for How to check if a database exists in SQL Server?

vote vote

95

Actually it's best to use:

IF DB_ID('dms') IS NOT NULL    --code mine :)    print 'db exists' 

See https://docs.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql and note that this does not make sense with the Azure SQL Database.

vote vote

87

From a Microsoft's script:

DECLARE @dbname nvarchar(128) SET @dbname = N'Senna'  IF (EXISTS (SELECT name  FROM master.dbo.databases  WHERE ('[' + name + ']' = @dbname  OR name = @dbname))) 
vote vote

73

IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'YourDatabaseName')   Do your thing... 

By the way, this came directly from SQL Server Studio, so if you have access to this tool, I recommend you start playing with the various "Script xxxx AS" functions that are available. Will make your life easier! :)

vote vote

62

I like @Eduardo's answer and I liked the accepted answer. I like to get back a boolean from something like this, so I wrote it up for you guys.

CREATE FUNCTION dbo.DatabaseExists(@dbname nvarchar(128)) RETURNS bit AS BEGIN     declare @result bit = 0      SELECT @result = CAST(         CASE WHEN db_id(@dbname) is not null THEN 1          ELSE 0          END      AS BIT)     return @result END GO 

Now you can use it like this:

select [dbo].[DatabaseExists]('master') --returns 1 select [dbo].[DatabaseExists]('slave') --returns 0 
vote vote

58

TRY THIS

IF EXISTS     (      SELECT name FROM master.dbo.sysdatabases      WHERE name = N'New_Database'     ) BEGIN     SELECT 'Database Name already Exist' AS Message END ELSE BEGIN     CREATE DATABASE [New_Database]     SELECT 'New Database is Created' END 

Top 3 video Explaining How to check if a database exists in SQL Server?

Related QUESTION?