You need to drop more databases quickly. If you want to drop some database you can use SSMS GUI or T-SQL to drop single database, but if you need to perform more complex cleanup for example after successful migration you can save your time by generating batch drop T-SQL and execute it within one script.
Only thing you need is to specify databases by wildcard on line 11. Script will generate drop T-SQL into messages then you can copy and execute it on instance you want to perform cleanup on.
Warning: be aware of executing generated script as it is dropping databases physically and only way to rollback is to restore database from backup, if you are lucky you have one 🙂
DECLARE @DB SYSNAME DECLARE @SQL VARCHAR(255) PRINT '-- Execute folowing script to drop databases in one batch' -- cursor for iterating through all databases DECLARE dbname CURSOR FOR SELECT name FROM sysdatabases WHERE name LIKE 'Report%' -- specify wildcard there (%_...) ORDER BY NAME -- itegerate through all databases OPEN dbname FETCH NEXT FROM dbname INTO @DB WHILE (@@fetch_status <> - 1) BEGIN IF (@@fetch_status <> - 2) BEGIN -- database backup history cleanup PRINT 'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N''' + @DB + '''' PRINT 'GO' -- database dropping SET @SQL = 'DROP DATABASE [' + @DB + ']' -- uncomment folowing line for direct execution -- EXEC (@SQL) PRINT @SQL PRINT 'GO' END FETCH NEXT FROM dbname INTO @DB END CLOSE dbname DEALLOCATE dbname
Script messages after execution:
-- Execute folowing script to drop databases in one batch (including all databases fitting wildcard specified on line 11) EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ReportServer' GO DROP DATABASE [ReportServer] GO EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ReportServerTempDB' GO DROP DATABASE [ReportServerTempDB] GO