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
I use the @DatabaseSearch variable to look for the databases with similar names.
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