SQL create database, if it does not exist, unexpected behavior

Question

I have a long stored procedure that starts with the following statement:

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

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

Other ways I've 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 fine, which makes me think that this may be a rights issue.

Answer 1

Try using

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

If this does not work, this may be permission. This explains why you are not getting an error message.

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

(From sys.d database in MS documentation )

What permissions does the user you work under have?

Try changing your code so that it simply returns the contents of sys.databases so that you can see it.

Answer 2

Inclusion, because I had a similar problem: I wanted to create a database if it does not exist, and then perform operations on this database.

I think the problem was that the script was trying to run in one package, so it was trying the USE database before the SQL server received the CREATE command. This led to the whole script being completely redesigned, and it seemed that the root of the problem was that the database was never created.

In my case, the solution was to add the GO command after the initial part of the script in which the table is created, but before I started working with it (for example, creating tables).

Answer 3

When comparing strings, use LIKE

 if (SELECT count(name) FROM sys.databases WHERE name LIKE '%DBNAME%') = 0