How to drop database when used by other users.

When ever we are droping or deleting the databse if it is used by other users. Sql server displays the following error message.

"cannot drop database "Database_Name" because it is currently in use"

To avoid this error message and close all the existing connections and drop immedeiately you need to use the following syntax.

Change the Database_Name with your Database name to drop.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Database_Name')
BEGIN
PRINT 'DROP DATABASE [Database_Name']'
ALTER DATABASE [Database_Name'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Database_Name']
END
GO