The first step would be to lift the current restrictions of ALTER DATABASE COLLATE. That is, CHECK constraints, computed columns and schemabound functions and view would be reparsed as part of the transaction, which would roll back in case of error. Table-valued functions is somewhat more problematic, but they should probably be recompiled too have their columns changed.
The next step would be to add a clause COLLATE WITH DATA. When this clause is in effect, ALTER DATABASE would update the collation of columns with the database collation(*)
in the metadata. All indexes involving these columns would be disabled, and all constraints involving these columns would be marked as not trusted. At this point ALTER DATABASE would commit its transaction and complete. Thus the database
would be left in a more or less unusable state.
The last step would be supply a stored procedure that rebuilds all disabled indexes, and checks all untrusted constraints. This procedure could embed ALTER DATABSE COLLATE WITH DATA_CHANGE. This procedure would continue even if there are errors and report all errors.
The reason for this two-step operation, is that if ALTER
DATBASE did all in one transaction, the log could grow enormously. The database could also be offline for a very
long time, and no one would like think of what would happen if the last index fails because of a duplicate in the new collation.
Since COLLATE WITH DATA_CHANGE would be quite a dangerous operation, it would need some safeguards, like requiring
single-user mode. Even strong serious safe-guards would be to put this in RESTORE or CREATE FOR ATTACH.
(*) Or you could track whether column was created with an explicit collation, and only change those that were not.