SQL 如果不存在则创建数据库，出现意外行为
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40838442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL create database if not exists, unexpected behaviour
提问by StefanL19
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.
如果它不存在，预计会在我的本地服务器上创建数据库。问题是几乎所有时间它都通过存储过程的这一部分而不创建它，然后干扰来自同一过程的其他代码。另一方面，在极少数情况下，它会创建数据库。我的问题是：有没有更好的方法来检查数据库是否存在，因为我已经尝试了至少 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.
但是如果我在我的 sp 之外运行它，它运行得很好，这让我认为它可能是一些与权限相关的问题。
回答by under
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
...查看相应行所需的最低权限是 ALTER ANY DATABASE 或 VIEW ANY DATABASE 服务器级权限，或 master 数据库中的 CREATE DATABASE 权限。调用者连接的数据库总是可以在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.
尝试更改您的代码以仅返回 sys.databases 的内容，以便您可以看到它。
回答by martonbognar
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 USEthe database before the SQL server received the CREATEcommand.
This resulted in the whole script getting reverted and it seemed like the root of the issue was that the database never got created.
我认为问题在于脚本试图批量运行，因此它USE在 SQL 服务器收到CREATE命令之前尝试了数据库。这导致整个脚本被还原，问题的根源似乎是数据库从未被创建。
In my case the solution was to add a GOcommandafter the initial part of the script where the table gets created but before I start working with it (e.g. creating tables).
在我的情况下，解决方案是在创建表的脚本的初始部分之后添加一个GO命令，但在我开始使用它之前（例如创建表）。
回答by user1054844
When comparing strings use LIKE
比较字符串时使用 LIKE
if (SELECT count(name) FROM sys.databases WHERE name LIKE '%DBNAME%') = 0
