Tutorial :How to check if a database exists in SQL Server?



Question:

What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.


Solution:1

From a Microsoft's script:

DECLARE @dbname nvarchar(128)  SET @dbname = N'Senna'    IF (EXISTS (SELECT name   FROM master.dbo.sysdatabases   WHERE ('[' + name + ']' = @dbname   OR name = @dbname)))    -- code mine :)  PRINT 'db exists'  


Solution:2

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


Solution:3

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! :)


Solution:4

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »