The best answers to the question “How to check if a database exists in SQL Server?” in the category Dev.
What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.
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)))
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.
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
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! 🙂