Programmatically Delete a database in SQL server

Sometimes we would like to programmatically delete SQL server database. Simply writing:

DROP DATABASE 'database_name'

is not always a good solution, for 2 main reasons:

1. The database may be already deleted, therefore we will get exception telling there is no such database to delete.
2. There may still be existing connections to the database, preventing from the database being deleted.

The first problem can be easily solved by checking the existence of the database before deleting it:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
DROP DATABASE 'database_name'
END
GO

Solving the second problem is a bit harder. In order to delete the database, all existing connections needed to be closed. This should be done carefully since we do not want to close our own connection. This script, first closes all existing connections and then drops the database:

DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
DROP DATABASE 'database_name'
END
GO

Note that the name of the database (“database_name” in this example) can be replaced with SQL parameter.