I manage a database which was created on a different server and has been restored on my server. The server collation on my server is Latin1_General_CI_AS, but the collation of the database in question is SQL_Latin1_General_CP1_CI_
AS. The collation is causing errors in my application so I need to change the collation of the database, all the tables, and all the columns.
I have done some searching and found some examples of changing the collation of a column:
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
But according to Microsoft (http://msdn.microsoft.com/en-us/library/ms190920(SQL.90).aspx
) there are restrictions:
You cannot change the collation of a column that is currently referenced by any one of the following:
* A computed column
* An index
* Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
* A CHECK constraint
* A FOREIGN KEY constraint
In the database I need to change there are about 1200 tables, and many thousands of columns. Many of the columns are either indexed or foreign keys.
I thought of writing a script which has two cursors - one to select the tables, and another to select the columns of the current table - which would go through each table and column and alter the collation. But what happens to all the columns which are indexed or foreign keys?
Can anyone suggest a reliable way to change the collation on ALL the columns, tables, and the database itself?