Changing Collation.

[knopmike]

I have a few databases that were imported from another SQL Server 2000 that I need to change the collation sequence for.

I'm aware of the alter database command, but after executing this command, the database collation changed but all of its tables still had the old collation sequence.

Is there any easy way to change a database's collation sequence as well as all of its tables?

Thanks Mike.

 

[tlbroadbent]

See the topic "Changing Collations" in SQL BOL.

Available online at:

http://msdn.microsoft.com/library/en-us/architec/8_ar_da_819v.asp

The article explains that changing the database collation doesn't change the collation for existing columns. It does explain how to change column collation as well as the limitations. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[knopmike]

Thanks for your help, however I don't have much experience writing scripts and it would take a long time to create a script for every column of every table of the databases I need converted.

Any additional help anyone could provide would be appreciated.

Thanks.

Mike

 

[tlbroadbent]

Consider this process.

1) Script the objects from the existing databases
2) Modify the scripts to use the correct collation
3) Create new databases with the correct collation
4) Create objects using the corrected scripts
5) Use DTS to transfer the data from the old tables to the new tables. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.