Great article. A few points:
1. remake your idempotent statements to "if not exist create, then alter", rather than drop/create.
if object_id(N'[schema].[sp_proc]',N'P') is null
exec ('create proc [schema].[sp_proc] as Select X = 1')
alter proc [schema].[sp_proc] (@some_variable bit = 1)
set nocount on
if @bit = 1
This technique (if not exists/create/alter) is important to preserve user and role permissions. Drop/create idempotent scripts wreak havoc on security, requiring a follow-up security script if you have anything other than a vanilla security schema.
2. Indexes - where do I begin? I have implemented a deterministic index/constraint naming convention at my company, modeled after the Microsoft naming conventions. I published the standard, and the developers (for the most part) adhere to it. In any case a job runs daily in staging/prod to locate and email incorrectly named objects, so we can fix the code long before it hits production. What does this buy us? We don't drop/create indices, ever. Since every index has a unique, deterministic name, the idempotent script is simply (for example):
if not exists (select top 1 1 from sys.indexes where name = 'IX_SCHEMA_table_K1_K3_i4')
create nonclustered index on [schema].
(column1, column3) include (column4)
Note the lower-case "i" in the convention -- makes it easily distinguishable from lower-case L. I'll grant that not having the column names in the index/constraint name will involve an extra step sometimes, but it removes the need to drop/create an index on a deep table AND allows easy checking for overlapping indexes, etc. Idempotency using drop/create for indexing can be quite expensive and time consuming. Further, it removes the very likely scenario where two developers would otherwise name a covering index with different names - resulting in index duplication. Just my thoughts - I'd be happy to post my naming convention documentation and code for sending out email for misnamed objects if anyone thinks these thoughts have any merit.