Interview Question : What is the ideal way to check if a database exists on a SQL Server using T-SQL?

There is various ways to check database is exists or not in SQL Server. I am sharing with you below Queries tested by me.

Scenario 1:-

   DECLARE @DBName NvarChar(200)
   SET @DBName = N'Your_Database_Name'
   
   IF EXISTS(SELECT name FROM master.dbo.sysdatabases 
             WHERE ('[' + name + ']' = @DBName  OR name = @DBName))
   BEGIN
     PRINT 'Database Exists';
   END
   ELSE
   BEGIN
     --You can write Database Create Statement Here.
     PRINT 'Database Created'
   END

Note:- If you have permission issues, like you don't have permission to access [master] this works properly.

Scenario 2:-

A DB_ID function [See https://docs.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql]

Syntax :

   DB_ID ( [ 'database_name' ] )

Here 'database_name', The name of the database whose database ID number DB_ID will return.
If the call to DB_ID omits database_name, DB_ID returns the ID of the current database.

Return types :

   Int

Examples : 
Returning the database ID of the current database

   SELECT DB_ID() AS [Database ID]
   Go

Returning the database ID of a specified database
SELECT DB_ID(N'Your_Database_Name') AS [Database ID] Go
I have used above function like below :
IF (DB_ID(N'Your_Database_Name') IS NOT NULL) BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. PRINT 'Database Created' END
Scenrio 3: We can also create a function using DB_ID() as below
Example :
     CREATE FUNCTION dbo.DatabaseExists(@DBName NvarChar(100)) 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


-- We can use above function like below:
   
Select [dbo].[DatabaseExists]('master') AS DatabaseExists --Returns 1 Select [dbo].[DatabaseExists]('Test') AS DatabaseExists --Returns 0



Scenrio 4: There is another simple way is available to check as below:

SELECT * FROM master.sys.databases WHERE name= N'Your_Database_Name'

Example: I am using this query in my way as below:

IF EXISTS(SELECT name FROM master.sys.databases WHERE name= N'Your_Database_Name') BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. PRINT 'Database Created' END


Scenrio 5: There is another simple way is available to check as below:

     IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'Your_Database_Name') BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. --CREATE DATABASE [Your_Database_Name] PRINT 'Database Created' END