SQL create database if not exists, unexpected behaviour

Question

I have a long stored procedure which begins with the following statement:

IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBNAME')
    BEGIN
        CREATE DATABASE [DBNAME]
    END;

It is expected to create the DB on my local server, if it does not exist. The problem is that almost all of the time it goes thorugh this part of the stored procedure and does not create it, which then interferes with the other code from the same procedure. On the other hand, in very rare cases, it creates the DB. My question is: Is there a better way to check if the DB exists, because I have already tried at least 10.

Other ways I tried:

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

IF  NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DBNAME')
        BEGIN
            CREATE DATABASE [DBNAME]
        END;

IF  NOT EXISTS (SELECT name FROM master.dbo.sys.databases WHERE name = N'DBNAME')
            BEGIN
                CREATE DATABASE [DBNAME]
        END;

But if I run it outside of my sp, it works perfectly, which makes me think that it can be some problem related to permissions.

Eindhoven

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBNAME') is already working in 2014 and showing correct result. Are you getting any error ? – Lorimer

have you used any roll back as part of ur proc ..if yes then check it is not getting rolled back.. – Hamman

No, I just get Database 'DBNAME' does not exist, when I try to use it in the stored procedure after that. I am not using a transaction. – Eindhoven

If you are using Transaction, Make sure you commit it. – Dori

MSDN Documentation should help! – Endocrinology

Please do not crosspost already asked at dba.stackexchange.com/questions/156516/… – Curiel

Answer 1

Try using

 If(db_id(N'DBNAME') IS NULL)

If that does not work, it could be the permissions. That would explain why you are not getting an error message.

...minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases

(From sys.databases on MS documentation)

What permissions does the user under which you are running has?

Try changing your code to just return the contents of sys.databases so you can see it.

Chorale

I obtain the same result. – Eindhoven

changed the answer to add the comments about permissions. Try that – Chorale

This is exactly what I came to this question for. @StefanL19 if this is the correct answer, please mark it as such. – Renee

Answer 2

The issue appears to be a lack of 'GO' to terminate the statements. This does not work...

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='dba')
    CREATE DATABASE [dba]

USE [dba]

But, this does...

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='dba')
    CREATE DATABASE [dba]
GO
USE [dba]

Troy. #

Tautomer

Answer 3

Chiming in because I had a similar issue: I wanted to create a database if it does not exist, then perform operations on that database.

I think the problem was that the script tried to run in one batch, so it tried to USE the database before the SQL server received the CREATE command. This resulted in the whole script getting reverted and it seemed like the root of the issue was that the database never got created.

In my case the solution was to add a GO command after the initial part of the script where the table gets created but before I start working with it (e.g. creating tables).

Astonishment