DISCLAIMER: I know that this question has been asked a hundred times before, but I just wanted to check that there wasn't an easier solution I might have missed before I went ahead and wrote/sourced lots of code to do it.
Our software uses a database that was originally designed for SQL Server 7, and as such, all the scripts that create it do not specify any explicit collation for any character columns. Instead, when a database is created/restored to SQL Server 2000 or above, every column inherits the database collation (which happens to be
SQL_Latin1_General_CP1_CI_AS since that was the SQL Server 7 default).
Theoretically, this would not matter too much, since if our database is created from scratch on a customer's server, it inherits the customer's server collation (which is normally the modern installation default,
Latin1_General_CP1_CI_AS) and everything just works. However, this scenario breaks down when they send us a database backup, or we send them a database backup, and either we or they get the dreaded collation mismatch error whenever the code tries to access temp tables etc.
We have tried educating customers to install or rebuild their SQL Server instances to use our preferred collation, but of course that doesn't always happen and it's not always possible.
Solutions that involve creating a new database and copying the data are not really practical for us, we need a "magic wand" that we can wave at a live database to correct all columns in-place without disturbing the data. I am thinking about writing a utility to do this, but since it will be quite a big job, does anyone have any simpler suggestions?