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
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'.
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;
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:
