Archive

Archive for December, 2011

T-SQL: How to drop all databases in SQL Server

Drop all the databases in an instance, easy right? manually deleting each databases is not big deal but what if you have more than 150 databases and you are asked to drop all?

We are not going to do it DB by DB rather we can automate it by creating a script to perform the actions.

I have tried something to implement this using metadata tables. This query below will list up the all the databases in the server from sys.databases and uses a cursor to iterate each db name. The dynamic sql inside the loop, drops the DB one by one.

This query may be useful when you have hundreds of Databases in an instance and you have an sudden requirement to clean up the server. This will be easier than to drop the databases manually.

Note:   Executing the query below will drop all the databases in the server. Carefully check the list of DBs by printing it (PRINT @sql). When you are sure and confirmed about the list of Databases that has to be dropped, go and uncomment the EXEC cmd (EXEC @sql) in script and run the query

Code:

-----------------------------------------------------------------------------------

 DECLARE @sql VARCHAR (max)
 DECLARE @DBname VARCHAR (50)
 DECLARE DBS CURSOR FOR
 SELECT name
 FROM   sys.databases
 WHERE  name NOT IN ( 'model', 'tempdb', 'master', 'model',
 'reportserver', 'ReportServerDB', 'msdb')
 OPEN DBS
 FETCH next FROM DBS INTO @DBname
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sql = 'DROP DATABASE ' + @DBname
 PRINT @sql
 --exec @sql
 FETCH next FROM DBS INTO @DBname
 END
 CLOSE DBS
 DEALLOCATE DBS

Happy Querying 😉

%d bloggers like this: