To change the collation of database Use following steps:
Just changing the collation of database doesn’t mean we change the collation of its tables and columns of tables. Change collation query needs to be executed on each of the table and its column individually.
In order to change the collation of database we need to drop following:
1. All check constraints
2. All Foreign Key Constraints
3. All the indexes
4. All Unique Key constraints
5. All views of the database.
After dropping above things we can execute query using " Collate " keyword to change collation of database, tables and all columns.
Once collation is changed we can re-apply all the above dropped things.
e.g. EXEC('ALTER DATABASE [' + @dbname + '] COLLATE '+ @srvrcollation)
Note: We don’t need to drop and re-create primary keys.
Please let me know if any one of us have used this approach in production.
I have wrote following script to change database collation to sql server default check the attachment
Last edited by Kautik; 09-08-10 at 02:28.
Reason: submiiting script