March 13, 2011 at 11:25 pm
on of my test server(sqlserver 2005 Dev Edition)all 10 DBs are
Latin1_General_CI_AS collation
As per reuirement i need to change the collation for All DBs with all objects so
How to change the collation of all the existing objects at once inside a databases & server
Thanks in advance.
March 15, 2011 at 8:56 am
I haven't successfully completed these steps but my suggestion would be
1. change server collation (this is important because you want tempdb matching db collation.)
3. database collation (should be same or compatible with tempdb)
4. BCP the data out (I suppose you could select into new table and resetup keys and indexes?)
5. rebuild the tables\views\... with correct collation.
I think you have to rebuild\recreate the objects for it to actually change the collation, correctly.
This is not an easy task and I don't think there is an easy answer.
hopefully someone can explain further the proper way if this is not.
March 15, 2011 at 9:06 am
You can find scripts online to do what you want although they are not guaranteed to catch every single object/column/etc. But AFAIK this action is UNSUPPORTED BY MICROSOFT. The only supported way to change collations that I know of is to export the data and import it into a new database with everything built with the new collation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 15, 2011 at 2:19 pm
One successful and repeatable way I've found to do this is with a tool such as PowerDesigner from SyBase although others will probably do the job too. Here's an overview of the steps I take:
1. Reverse engineer the database into a PowerDesigner physical data model.
2. Immediately save the model as an archived model. This will be used later. I save it in XML format.
3. Save the model again as a normal PowerDesigner model. Again, in XML format.
4. Edit the model as follows:
- Remove default constraint names given to those columns that have default values as PowerDesigner doesn’t seem to drop them when it renames the original table so we get duplicates. They get recreated when the table is created with a default anyway.
- Change the collation of all character columns, either to the desired collation or to database_default. I prefer the later. Note for a very large database I do this by editing the xml file by doing a search and replace. To go from SQL_Latin1_General_CP1_CI_AS to the new database default I search for 28=SQL_Latin1_General_CP1_CI_AS and replace with 16=database_default.
5. Save the model again.
6. Now choose Apply Model Changes. This is where we compare it against the archived model which is a representation of the "current" state of the database. At the end of this stage PowerDesigner will produce an SQL script to run in your database. However it still needs a tweak or two.
7. Edit PowerDesigner's script to:
- Add an alter database statement for the database itself.
- Add drop trigger statements before each trigger gets created as for some reason PowerDesigner has failed in this department for several versions.
8. Run the script. Make sure you have enough disk space because every table that needs altering will be duplicated as PowerDesigner will rename the original (usually tmp_mytablename), create a new one, then copy the data from old to new.
As always, test the process first. Also for a big database you may want to run the script in smaller pieces and take steps to manage logging etc.
Note that there is more detail to each step which is mostly related to how to use PowerDesigner to achieve what you want. Obviously this also doesn't cover how to change your server collation etc. and there may be other things that I haven't come across that may cause issues.
Also you may not have PowerDesigner but you may be able to download a 30 day trial that'll get you through, or better still convince somebody to pay the fee and buy it.
March 15, 2011 at 6:52 pm
As it happens, there is a new article just posted today at
http://www.sqlservercentral.com/links/306693/200479
with a rather impressive full scenario script.
Add to it the stored proc that changes the collation for each table (see code within the discussion) - which you may wrap in a script that iterates the stored proc for each database ( I thought it would be safer to do it manually).
Keep in mind TheSQLGuru's warning, all these steps will change the collation for the server, the databases default, then the table defs and will guarrantee consistency for the future, but it is not clear what happens to the data already existing in each table.
If the changed collation is not fundamentally different (such as CS vs CI - case sensitivity) or different character set altogether, you may want to take your chances with these scripts.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply