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

All SQL Server Session information in a SQLServer

How to get all session id's information which are connected to the database



select * from sysprocesses

How to display System database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid =1

How to display User Created Database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid <> 1