Script to drop all databases except one not working

Question

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(''?'') > 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

• LowlyDBA - John M: You can't drop the system databases. Also be aware that sp_MSforeachdb is an undocumented command and shouldn't be trusted outside of a dev/test environment (or even in, potentially). Check out making a more reliable sp_MSforeachdb
• Stackoverflowuser: You can't drop the system databases: IF (DB_ID(''?'') > 4 ...shouldn't be trusted outside of a dev/test : ...on my test server...

Answer 1

Would you like to try this?

DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' 
+ 'drop DATABASE [?] ' 
+ 'END' 
EXEC sp_MSForEachdb 
@command1 = @cmd1 
GO

it worked here.

PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.

• Racer SQL: sp_msforeachdb can be removed in future sql versions.

Answer 2

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 > 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 > 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 > 4 and name > @dbnamename and 
 name not in ('drop1','drop2','drop3') ---- CHANGE HERE FOR Databases that needs to be excluded !!
 end
end

• Stackoverflowuser: The script will be an agent job so no chance in opening another query window. Thanks.
• Kin Shah: @Stackoverflowuser I would not know that until you specify in your question. BTW, its easy to convert the above query and run the output using sp_executesql or exec.
• Stackoverflowuser: Thanks @Kin. I know I should have mentioned it would be an automated one.

Answer 3

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this....

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

• Stackoverflowuser: Sorry mate..Still no luck :(
• benjamin moskovits: I can probably write this using exec sp_executesql or are you committed to using sp_msforeachdb?
• Stackoverflowuser: I would like to learn the mistake I made but if sp_executesql is the only way around, please write it :)
• benjamin moskovits: The problem with EXEC sp_MSforeachdb is that often its very challenging to see where you fell of the cliff. Its an 'unsupported' feature but very widely used. Plus you are dropping databases so many users are not ready to just run it and see what happens....