How to automate the dropping of databases?

Question

Working on a script to drop databases.

In some of the environments our databases are regularly restored from live, replication (merge or transactional) established, processes run and tested, then, the databases need to be dropped, as all of the development has been deployed to live, now another cycle starts.

as part of automating the dropping of the database so far I came out with this script below:

select
DROP_DB_SCRIPT='use master;' + char(13) 
+ case when source_database_id is not null -- this is a database snapshot
 then '' 
 else 
 ' alter database ' + name + ' set single_user with rollback immediate ' + -- put db in single user mode 
 case when (sb.is_published = 1 or sb.is_merge_published = 1) 
 then 'EXEC sp_removedbreplication ' + '''' + name + '''' -- remove db from the replication 
 else '' 
 end +char(13) 
 end +char(13) +
' drop database ' + quotename(name) +char(13) 
,*
from sys.databases sb
where database_id >= 5
and is_distributor= 0

The script above will not do anything other than produce a script that I can use to drop a desired database or set of databases.

it takes into consideration replication, (you need to remove the replication separately), and if the database is a database snapshot, which is a different thing.

I have not considered the following:

Should an OFFLINE database be SET EMERGENCY before dropping?

the question here is: Is there anything, any check, that you can see I should have added but have not?

I have dealt with 2 things:

1. database is a snapshot
2. database is involved in replication

I have not dealt with:

1. Database is part of an availability group (in that case I would need to remove it from the AG first)
2. anything else that you can see?

Answer 1

database_id > 5 is not a good idea. There are some circumstances that the id of system database can be >= 5 (inplace update as an example)...

So, use :

sb.name NOT IN ('master, 'model', 'msdb', 'semanticdb', 'tempdb')

Also for AG you can use :

sys.fn_hadr_is_primary_replica (sb.name) = 1 
OR sys.fn_hadr_is_primary_replica (sb.name) IS NULL

• J.D.: If you're listing system databases by name, you should also include distribution (from Replication).
• Marcello Miorelli: very good point that you mentioned inplace updates
• Marcello Miorelli: distribution, I have some environments, with more than one distributor database, so the is_distributor works fine for me, but of course thanks for the reminder
• SQLpro: it is why I d not use the name "distribution" in the exclude list.
• Ronaldo: @MarcelloMiorelli once I asked if Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively? and in the answer 'ssisdb' was listed in addition to the databases listed in here. Maybe it would be useful to consider it here too.