How to change collation on column level which have PK or index in SQL 2005?
-
Hi, I want to change collation on column level in all tables in database and I have got error if column
have a PK or index. One alter ex:
alter table dbo.T_Case alter column [CID] varchar(128) collate SQL_SwedishStd_Pref_CP1_CI_AS not null
Error is:
Msg 5074, Level 16, State 1, Line 4
The object 'PK_T_Case' is dependent on column 'CID'.
Msg 4922, Level 16, State 9, Line 4
ALTER TABLE ALTER COLUMN CID failed because one or more objects access this column
Have tried with disable constraint and trigger but it dont work, still have the same error:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
QUESTION: What is the easeiest way to change collation on column level which have
PK or index on, in all tables in a database.
Great regrads.
/Semko
-
If you use management studio, you can modify a table, change an indexed column's collation and have SSMS generate the script it would execute. (cancel the modification!)
Have a look at the script and see what steps it will actualy perform to accomplish this.
With referenced (FK) primary key columns, you'll also have to modify the dependant objects ! So it will be best to remove the relationships, perform your modifications and put the DRI back in place.
Maybe you'll end up creating a new db with the new collation settings, if you don't mix collations
, script out the full old db's objects without collations, implementing them in the new db and import all data. Maybe even considering a detatch and attach operation to bring the new db online using the old db-name 
. If you alter a database's default collation, it will not modify existing objects, but will only use it when creating new objects.
Prepare your operation and test it more than once.
I wouldn't recomend such an operation on a live database !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks,
but if I have lot of tables it will take time and you mean I must drop and recraete PK and index to do it?
/Semko
-
indeed
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks, just to do it.
/Semko
-
Hi, have you some script to script index and PK?
/Semko
-
I think you can find some at SSC.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply