Drop databases on a server conditionally from a List

Question

I have created a list of databases I want to drop based on a set of conditions and have titled it delete_table in the master database (this already excludes master, tempdb, model and msdb). The only column in this table is name which contain the exact database names that should be deleted.

How would I go about writing the script that would drop the databases on the server based on this list?

Thanks!

Bryan · Accepted Answer · 2012-09-07 14:54:37Z

2

If you want a quick way to script out DROP DATABASE commands:

--set database in single user mode, rolling back active transactions...be careful!
SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' FROM sys.databases WHERE name IN (...)
UNION
--drop database
SELECT 'DROP DATABASE ' + QUOTENAME(name) + ';' FROM sys.databases WHERE name IN (...);

Replace ... with a comma separated list of database names (e.g N'DatabaseOne', 'DatabaseTwo', etc.). Run the select query, then use results for execution.

I don't understand the need for a user table to solve this. As a side note, you should avoid creating user objects in master database. Granted, I can't think of a reason other than one based on aesthetics, but it just seems wrong.

More on user-created objects in system databases...

MSDN states user objects shouldn't be created in master, but I think the reason provided is pretty weak. A more substantive argument involves lack of control over what happens to system database objects during service pack/version upgrades.

I'm not saying the next service pack upgrade will wipe out your user created objects in master, but who knows. Put your utility and administration-type objects in a user created database so there's no confusion.

edited Sep 7, 2012 at 14:54

answered Sep 6, 2012 at 19:22

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

paparazzo Over a year ago

Agree on not creating user object in master.

harambissimo Over a year ago

What if I were to delete the tables I created after the script was completed?

Bryan Over a year ago

I don't see why the table of database names is needed. Why not put your conditions directly in the query provided above? Tell us what your conditions are for dropping a database, and we can incorporate into the solution.

harambissimo Over a year ago

The way I have it working now is I create a table containing the date when the database was created and when it was last accessed. The condition I have set is that the currentdate - createdate/lastaccessdate > 30. The databases that meet this condition are listed down in the delete_table table. I'm fairly new to SQL and this is the only way I knew to go about doing this

Bryan Over a year ago

OK, how are you determining the last access date?

|

ClearLogic · Accepted Answer · 2012-09-07 04:06:24Z

1

quick way

DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL+ 'DROP DATABASE ' + QUOTENAME(name) + ';' 
FROM delete_table
EXECUTE (@SQL)

a bit safer way to make sure database exists and each database get scripted only once incase you have dups

DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL+ 'DROP DATABASE ' + QUOTENAME(name) + ';' 
FROM sys.databases WHERE name IN (SELECT name FROM delete_table); 
EXECUTE (@SQL)

Edit to add alter database simply add this line

DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL+ 
'ALTER DATABASE '+ QUOTENAME(name) + 'SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' +
'DROP DATABASE ' + QUOTENAME(name) + ';' 
FROM sys.databases WHERE name IN (SELECT name FROM delete_table); 
EXECUTE (@SQL)

edited Sep 7, 2012 at 4:06

answered Sep 6, 2012 at 20:04

ClearLogic

3,7521 gold badge27 silver badges33 bronze badges

2 Comments

harambissimo Over a year ago

Thanks! this works quite nicely. One last thing, is there a way to do the: ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE command easily for all the databases?

ClearLogic Over a year ago

I have added the "alter database set single user..."

Collectives™ on Stack Overflow

Drop databases on a server conditionally from a List

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related