SQL Server query if then logic

Question

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

Answer 1

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

Answer 2

Samsam, you can try this:

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

Answer 3

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.

Correct : ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

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