Introduction

I had these similar databases on my dev environment which I had to delete multiple times during development. As this process was becoming more and more mechanical, I wanted to have a script which would do this for me.

Using the code

Below is the script which looks for the database(s) with name pattern and deletes them. I use the @DatabaseSearch variable to look for the databases with similar names.

 

[codesyntax lang=”sql”]

SET NOCOUNT ON

DECLARE @DatabaseSearch nvarchar(48)
DECLARE @Sql nvarchar(max)

SET @DatabaseSearch = 'DeleteDatabasesLikeThis%'

DECLARE @DatabaseName nvarchar(48)

SELECT
[Name] AS [DatabaseName]
INTO #Databases
FROM sys.databases
WHERE
[Name] LIKE @DatabaseSearch

DECLARE DBNameCursor CURSOR FOR SELECT DatabaseName FROM #Databases
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql =
'ALTER DATABASE [' +
@DatabaseName +
'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' +
'DROP DATABASE [' + @DatabaseName +'];'

EXEC(@Sql)

FETCH NEXT FROM DBNameCursor INTO @DatabaseName
END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor

DROP TABLE #Databases

[/codesyntax]