asked on

using BEGIN END with IF statement..

how can the below logic be made to work? (the goal is to delete a database only if it exists.. then this will be part of automated code.. the need for SINGLE_USER logic is to make sure the database is not in use when tried to be deleted..)

USE MASTER

IF
(
SELECT COUNT(*) FROM sys.databases WHERE name = 'db1'
)
=1
BEGIN
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
DROP DATABASE db1
END

GO

Anil Golamari

Below code can be used to drop the database.

USE MASTER

IF  EXISTS 
(SELECT name FROM master.dbo.sysdatabases WHERE name = 'Sample')
BEGIN
USE Sample
ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
USE master
DROP  DATABASE Sample 
END
ELSE
BEGIN
	SELECT 'database does not exist'
END

Open in new window

25112

ASKER

anil, your code produces this error:

Msg 911, Level 16, State 1, Line 6
Database 'Sample' does not exist. Make sure that the name is entered correctly.

Anil Golamari

I have created a database 'Sample' on my local and then used above code to delete the Sample database. Now if I run above code on my machine I get same error message. So please try to replace the database name 'Sample' with you test database name.

25112

ASKER

if database does not exist, should not this line be run:
SELECT 'database does not exist' (line #13)

SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Try..

USE MASTER
GO

IF  EXISTS ( SELECT TOP 1 1 FROM MASTER.DBO.SYSDATABASES WHERE name = 'db1')
BEGIN
               
		ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE		
		DROP  DATABASE db1 
END

Open in new window

25112

ASKER

>>
ALTER DATABASE Sample SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>>

if you do SINGLE_USER without using
[USE SAMPLE]
prior,
another process could take hold of database, right? then the drop wont work??

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

25112

ASKER

this is test server..

---
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
---
will make sure you are the only user In the database..
then if you have the next line as
USE MASTER,
the database is likely to be ready to be dropped, if that is the process..

but, the below
---
USE master
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
---
how can that guarantee that all process has been kicked out of the database.. only one user can be in the database and it may not be you, because you are in master database, in this code segment.. not db1?

25112

ASKER

>> you don't need to switch context to run the ALTER DATABASE statement:
carl, the context is not on ALTER, as much as keep the database free of all processes to be able to be dropped..

Pawan Kumar

Hi 25112,
Whats the update?

Is it done?

25112

ASKER

confirmed. thanks.