Search This Blog

Showing posts with label How to drop all the user databases in one GO. Show all posts
Showing posts with label How to drop all the user databases in one GO. Show all posts

Wednesday, September 2, 2009

How to drop all the user databases in one GO

It was Saturday Morning and I had a task @ hand .
One of my collegues wanted me to write a script to drop all the user databases at one go ..

I was initially reluctant as I thought any DDL can not take variables .But then I tried with some other stuff which did not use variables with DMLs.

Initially I came up with the script below :

Create table drop_db(name sysname)
Insert into drop_db select name from sysdatabases where dbid >4
Select 'drop database ' + name from db_name

Copy the output and execute.You canuse the same trick for other purposes as well .

But then I wanted to give it some more try and came up with the final script .

Drop table #db_name
Go
Create table #db_drop (name sysname,flag int identity (1,1) )
Insert into #db_drop select name from sysdatabases where dbid>4
Declare @@flag1 int
Select @@flag1 =max (flag) from #db_drop

While @@flag1 >0
Begin
Declare @string nvarchar (30)
Declare @dbname sysname
Select @dbname =name from #db_drop where flag=@@flag1
Print @dbname + ' is being deleted'
Set @string =' drop database ' + @dbname
Execute sp_executesql @string
Set @@flag1 =@@flag1 +1
End
Drop table #db_drop