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