I'll be as terse as possible... Our next product release will support
UNICODE, so all VARCHAR fields, casts, variables, and all usage will be
changed to NVARCHAR.  Hundreds of our customers databases will need to be
programatically upgraded.  We know how the databases SHOULD look; fields,
indexes, types, constraints, defaults, views, etc.

My technique is to first drop all SPs and VIEWS, then do benign ALTER TABLE
commands to eliminate UDF use; we'll drop/add UDFs redefined as equivelent
NVARCHAR later.  (I'm preparing to "alter table alter column" to actually
change several hundred fields from VARCHAR to NVARCHAR.)  Next, I drop
constraints/indexes/defaults (named and unnamed) from any VARCHAR fields.
Next, drop UDFs then add NVARCHAR UDFs with same names; this should be
possible at this point.  Next, all the "alter table alter column" commands,
where the actual conversion from VARCHAR to NVARCHAR occurs.  Finally, I
recreate all constraints/indexes/defaults that were dropped, recreated views
and procedures.  There are hundreds of fields.  (Some changes exceed the
~8039 byte row size, so we are shrinking some of them too.)

Anyway, it works, usually.  I keep batches very small, and use simple
recovery mode during the conversion, and DB growth is typically much less
than I would have expected.

Finally, my problems... Our customers often add SPs, and sometimes special
indexes to support their specific needs, or specific performance issues.  At
the point where I am about to  "alter table alter column" and/or
"sp_droptype/sp_addtype" I'd like to warn and abort, rather than let some
rogue dependency cause failure.  (If that happens, so be it, our customers
will be on alert as will our support staff.)  I've looked at sp_depends and
sp_msdependencies, but neither seem quite appropriate.  I have a script I
used for a similar (2-byte numeric to 4-byte conversion) that reads
syscolumns/systypes/sysobjects/sysindexes/sysindexkeys, but dang!

Any words of help or guidance will be greatly appreciated.  This is a tough
project given the hundreds of fields and types, and further by the hundreds
of field databases across the planet.  Any reporting I can do prior to
actual "error" will help a little bit.

Thanks for reading, and thanks in advance for your words.  Sincerely,

James