In some scenarios we can find different SQL Server collations between the server instance and its databases. Sometimes the collation is fixed in some table columns or inside stored procedures to solve some of relationship problems with columns that have different collations. The most common technique to have a consistent collation for the instance and all it databases has a lot of steps and can sometimes be problematic.
A classic example is that when you want to reuse a SQL Server for System Center components and the Collation is not SQL_Latin1_General_CP1_CI_AS (required for the installation)
To correct the collations in a few steps is outlined in this Post. It is recommended to create a backup of all databases (including system databases) before take administrative actions on a SQL Server instance. It is important to ensure that there is no fixed collation logic in columns or inside stored procedures, triggers, etc., otherwise the command below may report problems.
In this post I will change "Latin1_General_CI_AS" collation to "SQL_Latin1_General_CP1_CI_AI"
In this post I will change "Latin1_General_CI_AS" collation to "SQL_Latin1_General_CP1_CI_AI"