3

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

  • 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 – LowlyDBA Mar 26 '15 at 16:10
  • You can't drop the system databases: IF (DB_ID(''?'') > 4 ...shouldn't be trusted outside of a dev/test : ...on my test server... – Stackoverflowuser Mar 26 '15 at 16:12
3

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.

  • sp_msforeachdb can be removed in future sql versions. – Racer SQL Mar 26 '15 at 16:51
4

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
  • The script will be an agent job so no chance in opening another query window. Thanks. – Stackoverflowuser Mar 26 '15 at 16:29
  • @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. – Kin Shah Mar 26 '15 at 16:34
  • Thanks @Kin. I know I should have mentioned it would be an automated one. – Stackoverflowuser Mar 27 '15 at 7:34
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
  • Sorry mate..Still no luck :( – Stackoverflowuser Mar 26 '15 at 16:17
  • I can probably write this using exec sp_executesql or are you committed to using sp_msforeachdb? – benjamin moskovits Mar 26 '15 at 16:23
  • I would like to learn the mistake I made but if sp_executesql is the only way around, please write it :) – Stackoverflowuser Mar 26 '15 at 16:28
  • 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.... – benjamin moskovits Mar 26 '15 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.