Problem: Your SQL server instance is running in Latin1_general_100_BIN collation. You restore a database from testing or from different environment with different collation say Latin1_general_100_BIN2. You realize that the database should be in the BIN collation and not in BIN2. So you go to properties of the database in SSMS(SQL Server 2005 /2008) and change the collation under options section. Still you get error messages on equal operations whenever you run a query or Stored Procedure.
A little further investigation reveals that the columns of some of the tables are still in BIN2 collation. How do you change the collation of all the columns from BIN2 to BIN?
Tthe code provided first checks for the database collation and lists out all the columns that are different from database collation. Once you get the list of the columns, the script changes the collation of the columns to database collation.
Note: We cannot alter columns that are depended. Something like Foreign keys, Check constraints, and Clustered indexes implies Primary keys. How ever if you have too many columns in too many tables then this code would definitely help you changing the collation.