How to drop or delete all User Databases on MS SQL Server

Drop or delete all User Databases on MS SQL Server

All of the databases on the SQL Server instance can be deleted using the script below, which will first kill all connections to the databases before deleting 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