Drop all Databases (+the option to exclude one)

, 2005-08-26 (first published: )

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)

Related content

What is the Maximum Page Size in SQL Server 2000?

I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.

4.55 (11)

2006-05-19 (first published: )

36,462 reads

SQL Server 2000 User-Defined Functions White Paper

The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.

2001-08-31

2,233 reads

Work Around Errors in Database Web Apps

ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.

2001-07-13

1,579 reads