Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database

Delete Similar Databases

5.00/5 (2 votes)
31 May 2012CPOL 5.9K  
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 mechnical, I wanted to have a script which would do this for me.

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.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)