SQL Server查询，如果那么逻辑

IF (EXISTS (SELECT name 
            FROM master.dbo.sysdatabases 
            WHERE name = 'db'))
THEN
    ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

Sorry for the lame question but all I want to do is if the database exist then alter it

抱歉这个蹩脚的问题，但我想要做的就是数据库是否存在然后改变它

3 个解决方案

#1

Another approach can be like following.

另一种方法可能如下。

if db_id('db') is not null
begin
 ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; 
end

Note that with ALTER DATABASE you need to specify the db name as literal, 'db' will not work.

请注意，使用ALTER DATABASE，您需要将db名称指定为文字，'db'将不起作用。

Correct : ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

正确：ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

You will get Error : ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

您将收到错误：ALTER DATABASE'db'SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

#2

T-Sql doesn't have a then keyword as part of the if statement. Source https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-2017. Just remove the word 'then'.

T-Sql没有then关键字作为if语句的一部分。来源https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-2017。只需删除“然后”一词即可。

#3

Samsam, you can try this:

Samsam，你可以试试这个：

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'db')
BEGIN
    SELECT 'Database Name already Exist' AS Message
END
ELSE
BEGIN
    ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; 
END

Reference:

参考：

Alter table if exists or create if doesn't
如果存在则更改表或如果不存在则创建
http://sqlserverplanet.com/dba/check-if-database-exists
http://sqlserverplanet.com/dba/check-if-database-exists

#1