Drop All user databases

-- Drop all user databases

DECLARE @DBName NVARCHAR(255);

DECLARE @SQL NVARCHAR(MAX);

-- Cursor to go through all user databases except 'test' and system databases

DECLARE DropDBCursor CURSOR FOR

SELECT name

FROM sys.databases

WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'test','db_perf_reports')

AND state_desc = 'ONLINE' -- Only consider online databases

AND is_distributor = 0 -- Exclude distributor databases

 

OPEN DropDBCursor;

FETCH NEXT FROM DropDBCursor INTO @DBName;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Drop each database

    SET @SQL = 'DROP DATABASE [' + @DBName + ']';

    PRINT @SQL;  -- Print statement for reference

    EXEC sp_executesql @SQL;  -- Execute the drop

 

    FETCH NEXT FROM DropDBCursor INTO @DBName;

END

CLOSE DropDBCursor;

DEALLOCATE DropDBCursor;