Does MSSQL support an equivalent form of the MySQL statement `CREATE DATABASE IF NOT EXISTS {db}`?

Question

I'm using SQLAlchemy to execute SQL queries against different database engines.

For MySQL, I use the following query to create a database, if it doesn't already exist:

CREATE DATABASE IF NOT EXISTS {db} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Does MSSQL support an equivalent form of the MySQL statement CREATE DATABASE IF NOT EXISTS {db}? to create a database, if it doesn't already exist?

Not that I know of. I also can't think of a use case, when are you uncertain whether a database exists or not? — HoneyBadger, Sep 28 '20 at 13:34
Does this answer your question? [SQL create database if not exists, unexpected behaviour](https://stackoverflow.com/questions/40838442/sql-create-database-if-not-exists-unexpected-behaviour) — Shadow, Sep 28 '20 at 13:40

Thom A · Answer 1 · 2020-09-28T13:48:01.647

SQL Server doesn't have CREATE {object} IF NOT EXISTS syntax. It does, however, have CREATE OR ALTER syntax, but only for objects where a CREATE/ALTER must be the only statement in the batch (such as PROCEDUREs and VIEWs), and DROP IF EXISTS.

If you explicitly want to check if an object exists before you create it, and it doesn't support the CREATE OR ALTER syntax, you would need to use a statement prior to check for its existence:

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'testing')
    CREATE DATABASE Testing;

If, for some reason, a LOGIN does not have access to see the databases in sys.databases you could wrap the attempt in a TRY...CATCH:

BEGIN TRY
    CREATE DATABASE Testing;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1801
        PRINT N'Database already exists.';
    ELSE
        THROW;
END CATCH

Does MSSQL support an equivalent form of the MySQL statement `CREATE DATABASE IF NOT EXISTS {db}`?

1 Answers1