The existing Database connection can be changed using the ALTER Database command provided in SQL Server. To change the collation settings
- Restrict the Database access to Single User Mode
- Change the Database collation using the following syntax.
ALTER DATABASE dbname COLLATE [Replace Actual Collation]
GO
This will only change the collation of the Database and not the collation of the Database objects (Tables) if any exists already. We need to explicitly change the collation of the character (VARCHAR / NVARCHAR) columns for each table. To change the collation settings for columns
- Take the backup of all the Indexes and constraints of the tables with Character columns in the Database
- Drop all the Indexes and constraints (Primary Key, Foreign Key, Defaults etc)
- Change the Collation setting for the table columns. The below script can be used to generate the script that identifies the character columns and replace it with the new collation in the Database
SELECT 'ALTER TABLE ' + SYSOBJECTS.Name + ' ALTER COLUMN ' + SYSCOLUMNS.Name + ' ' +
SYSTYPES.name + '(' + RTRIM(CONVERT(CHAR,SYSCOLUMNS.length)) + ') ' + ' COLLATE [Replace Actual Collation]' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' End +
CHAR(13) + ' GO'
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
GO
