jeudi 26 mars 2015

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





Aucun commentaire:

Enregistrer un commentaire