How to Drop or delete all User Databases on SQL Server Instance - SQL Server Tutorial

Scenario:

You are working as DBA or developer, you need to generate scripts that should be able to drop all the users databases on SQL server Instance. 

Also you can made modification to Select query to include or exclude database which you would like to drop.

Below script can be used to drop all the databases on SQL Server Instance, It will kill connections to databases and then drop them.

USE MASTER
GO

DECLARE @DatabaseName AS VARCHAR(128)

DECLARE Cur CURSOR FOR

--Get list of Databases You want to drop
  SELECT name from
sys.databases
where database_id>4

OPEN Cur
FETCH Next FROM Cur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
  BEGIN
  
--Inner  Cursor Start
--Kill all user connections to database
  DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
 SELECT spid
FROM   sys.sysprocesses
WHERE  dbid = DB_ID(@DatabaseName) 
OPEN KillProcessCur
FETCH Next FROM KillProcessCur INTO @Spid
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END
CLOSE KillProcessCur
DEALLOCATE KillProcessCur
--Inner Cursor Ends

--Outer Cursor: Drop Database
      DECLARE @SQLDropDB NVARCHAR(MAX)=NULL
      SET @SQLDropDB='Drop Database ['+@DatabaseName+']'
      Print @SQLDropDB
      EXEC (@SQLDropDB)
      FETCH Next FROM Cur INTO @DatabaseName
  END
CLOSE Cur
DEALLOCATE Cur

--Get list of existing databases
Select name as DBName,@@serverName AS ServerName
from sys.databases