CODEWITHSUNDEEP
sql-server-2008t-sql
harambissimo
harambissimo

Reputation: 13

Drop databases on a server conditionally from a List

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!

Upvotes: 1

Views: 850

Answers (2)

Bryan
Bryan

Reputation: 17693

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.

Upvotes: 2

ClearLogic
ClearLogic

Reputation: 3682

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)

Upvotes: 1

Related Questions