How to check if a database exists in SQL Server?

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.

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

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

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

  Public Function SQLDatabaseExist(ByVal DefaultConnectionString As String, ByVal DataBaseName As String) As Boolean
Try
    'CREATE DATABASE
    Dim SqlString As String = ""
    SqlString = "SELECT CASE WHEN EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DataBaseName & "') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END"
    Dim ExcRet As Integer = 0
    Using connection As New SqlConnection(DefaultConnectionString)
        Dim command As New SqlCommand(SqlString, connection)
        command.Connection.Open()
        ExcRet = command.ExecuteScalar()
        command.Connection.Close()
        command.Dispose()
    End Using
    Return ExcRet
Catch ex As Exception
    Return False
End Try

End Function

''Notice the initial catalog in the connection string must be master! 'Sample Default Connection String

Dim DefaultConnectionString As String = "Data Source=localhost\SQLSERVER2008;Initial Catalog=Master; User ID=SA; Password='123123'; MultipleActiveResultSets=false; Connect Timeout=15;Encrypt=False;Packet Size=4096;"