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.

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 ? — Dhwani
– Dhwani, Commented Nov 28, 2016 at 6:47
have you used any roll back as part of ur proc ..if yes then check it is not getting rolled back.. — sandeep rawat
– sandeep rawat, Commented Nov 28, 2016 at 6:48
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. — Lazov
– Lazov, Commented Nov 28, 2016 at 6:49

under · Accepted Answer · 2019-07-01 21:23:00Z

30

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.

edited Jul 1, 2019 at 21:23

answered Nov 28, 2016 at 6:49

under

3,1072 gold badges28 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lazov Over a year ago

I obtain the same result.

under Over a year ago

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

Chad Over a year ago

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

Troy Frericks · Accepted Answer · 2020-06-10 20:14:12Z

3

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. #

answered Jun 10, 2020 at 20:14

Troy Frericks

1037 bronze badges

Comments

martonbognar · Accepted Answer · 2018-06-27 15:18:04Z

2

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

answered Jun 27, 2018 at 15:18

martonbognar

4623 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL create database if not exists, unexpected behaviour

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related