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.
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
Be First to Comment