Change collation of database (including all objects)

Restoring a backup of a database from Server A to Server B can result in issues if the server level collations differ. The restore of the database will succeed, however, the collation of the restored database will remain as it was on Server A, and will not inherit the collation of Server B. To change the collation of the restored database to match Server B's collation, perform the following steps:

1, Change the collation at database level as follows:

alter database [DatabaseName] collate [Collation]

 2, Next, change the collation of all columns in all tables. Changing the collation of columns within the database will fail, if there are constraints enabled on any of the columns. Before changing any collations at column level, disable the constraint checking for each table by running the following:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

3, After constraint checking has been disabled, change the collation of all objects within the database, as follows. Run the following SQL. Copy and paste the output into a new window, and run:

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
GO

 4, Check the output to ensure the previous script completed for each column (i.e. check there were no errors). Finally, re-enable the constraint checking for each table by running the following:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"