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, Nov 28 '16 at 06:47
have you used any roll back as part of ur proc ..if yes then check it is not getting rolled back.. — sandeep rawat, Nov 28 '16 at 06: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. — StefanL19, Nov 28 '16 at 06:49
[MSDN Documentation](https://msdn.microsoft.com/en-us/library/ms186274.aspx) should help! — N00b Pr0grammer, Nov 28 '16 at 06:55
Please do [not crosspost](http://meta.stackexchange.com/q/64068/157328) already asked at http://dba.stackexchange.com/questions/156516/sql-create-database-if-not-exists-unexpected-behaviour — , Nov 28 '16 at 06:58

under · Answer 1 · 2019-07-01T21:23:00.177

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 01 '19 at 21:23

answered Nov 28 '16 at 06:49

under

2,519
1
21
40

I obtain the same result. – StefanL19 Nov 28 '16 at 06:51
changed the answer to add the comments about permissions. Try that – under Nov 28 '16 at 07:26
This is exactly what I came to this question for. @StefanL19 if this is the correct answer, please mark it as such. – Chad Nov 14 '19 at 23:27

score 3 · Answer 2 · answered Jun 10 '20 at 20:14

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

score 2 · Answer 3 · answered Jun 27 '18 at 15:18

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

SQL create database if not exists, unexpected behaviour

3 Answers3

Linked