Technical Article

Drop all Databases (+the option to exclude one)

,

I made this Query because we fix databases localy on a workstation (we have no sql server) but when backup up the server they need to be deleted localy that could sometimes take a while.
U can run this Query from the QA or ad it as a Job it's possible to run it from an executable.

A little more about Keepdb and Previewdb.
If you want/need to keep more databases just add the line:
AND name NOT IN ('Keepdb1','Keepdb2','Keepdb3')
If you want/need to keep just one add the line:
AND name NOT IN ('Keepdb1')
Type the line right below this line:
SELECT name FROM sysdatabases WHERE sid 0x01

Previewdb: It means that you can see which databases will be dropped make sure that it looks like this
-- before: exec sp_executesql @SQL
print @SQL
If you happy with the result you can just remove the -- and print @SQL
Happy dropping

/*
Created:     18/08/2005
Created By:  QtombeD
Purpose(s):  Drop all Databases after a hard days work
Explenation: Will drop every Database except system
Keepdb:      To keep a database(s)add:
             AND name NOT IN ('Keepdb1','Keepdb2')
             or more if really needed (above is for 2)
             right under the line:
             SELECT name FROM sysdatabases WHERE sid <> 0x01
Previewdb:   add: -- before: exec sp_executesql @SQL and
             type print @SQL under it.
             It should read like this:
             --exec sp_executesql @SQL
             print @SQL   
Last Modified:  19/08/2005    
*/USE master 
GO 
DECLARE @dbname sysname 
DECLARE @SQL nvarchar(1000) 

DECLARE cur CURSOR FORWARD_ONLY KEYSET FOR 

SELECT name FROM sysdatabases WHERE sid <> 0x01 

OPEN cur 
FETCH NEXT FROM cur INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @SQL = N'DROP DATABASE ' + @dbname 
exec sp_executesql @SQL
FETCH NEXT FROM cur INTO @dbname 
END 
CLOSE cur 
DEALLOCATE cur

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)