Script to drop all databases except one not working I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error:

EXEC sp_MSforeachdb 
'
USE [?]
IF (DB_ID(''?'') &gt; 4 AND DB_NAME()!=''ABC'')
BEGIN
ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ''?''
END
'


Here is the error message:

Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'master'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'IMMEDIATE'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'tempdb'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'IMMEDIATE'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'model'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'IMMEDIATE'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'msdb'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'IMMEDIATE'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'ABC'.  
Msg 102, Level 15, State 1, Line 5  
Incorrect syntax near 'IMMEDIATE'. 


Where should I correct here?
Thank you
How about something like below :

change your output in SSMS to text and once you are happy run the generated sql statements in another query window :

use master 
go    
select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go'
from sys.databases
where database_id &gt; 4 -- exclude system databases
and name not in ('') -- exclude databases that you want to SAVE !!


Update :  Below is the script that will work in sql agent as well... 

/****************************************************************************************
Author      :   Kin Shah
                written for dba.stackexchange.com

Purpose     :   Drop all the databases except system and user defined 


Notes       :   1. EXCLUDE databases name not in ('drop1','drop2','drop3')              
                2. Remove the commented ---exec (@sqltext) to ACTUALLY drop the databases
******************************************************************************************/

declare @dbname sysname
declare @sqltext nvarchar(max)

begin
    select @dbname = min(name) from sys.databases where database_id &gt; 4 and 
    name not in ('drop1','drop2','drop3')  ---- CHANGE HERE FOR Databases that needs to be excluded !!

    while @dbname is not null

    begin
        select @sqltext = 'ALTER DATABASE '+quotename(name)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
        FROM sys.databases where name = ''+@dbname+'' 

        select @sqltext = @sqltext+ char(10)+ 'DROP DATABASE '+QUOTENAME(name)+';'  FROM sys.databases where name = ''+@dbname+''

        print @sqltext

        ---exec (@sqltext)

        select @dbname = min(name) from sys.databases where database_id &gt; 4 and name &gt; @dbnamename and 
        name not in ('drop1','drop2','drop3') ---- CHANGE HERE FOR Databases that needs to be excluded !!
    end
end