Update Collation of all fields in database on the fly

To fix this problem, you need far more fire power then this script provides. I tried the script and ran into problems with dependent objects that couldn't update: indexes, keys and procedures. The final solution took just 5 minutes with this code project app. The app says it is for Sql Server 2000 but I used it successfully with 2008.

http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

I can't stress this enough. BACKUP YOUR DATABASE. I had to use my backup three times to complete this task.

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

Reference: Setting and Changing the Database Collation

If there are too many columns, you can loop through SYS.COLUMNS to apply the ALTER TABLE statement.

How about:

DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'

SELECT
    'ALTER TABLE [' + TABLE_SCHEMA  + '].[' + TABLE_NAME + '] '
  + 'ALTER COLUMN [' + COLUMN_NAME + '] '
  + DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH 
        WHEN -1 THEN 'MAX' 
        ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') '
  + 'COLLATE ' + @collation + ' '
  + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM INFORMATION_SCHEMA.columns
WHERE  COLLATION_NAME IS NOT NULL
AND COLLATION_NAME <> @collation

Just in case anyone looking at this is using SQL server 2008, i had to make a couple modifications:

SELECT 'ALTER TABLE [' + sys.objects.name + '] ALTER COLUMN ['
+ sys.columns.name + '] ' + sys.types.name + 
    CASE sys.types.name
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE sys.columns.max_length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,sys.columns.max_length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_BIN ' + CASE sys.columns.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM sys.columns , sys.objects , sys.types
    WHERE sys.columns.object_id = sys.objects.object_id
    AND sys.objects.TYPE = 'U'
    AND sys.types.system_type_id = sys.columns.system_type_id
    AND sys.columns.collation_name IS NOT NULL
    AND NOT ( sys.objects.NAME LIKE 'sys%' )
    AND NOT ( sys.types.name LIKE 'sys%' )