When you have different developers, customers and databases your database collation is bound to get out of sync. Making sure you have matching collation is important because it can cause problems when it comes to running SQL queries and can impact performance if you are joining text fields. There isn't an easy way to change the collation across an entire server because the collation is stored against the fields in each table. I looked at a number of different scripts but found the following script was the easiest way to fix the problem. Simply set @toCollation to the collation you want in your database, and run the query. The query will output a List of ALTER COLUMN SQL Queries which you can copy into a query window and execute to fix your database.
declare @toCollation sysname
SET @toCollation = 'Latin1_General_CI_AS' -- Database default collate
SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation
You will find most of the queries will work, for the queries that dont work due relational joins you can open the table in enterprise manager in design mode and fix the small number of remaining offending fields manually really quickly. Within moments you have a database with matching collation accross all its tables.