I'm trying to write an upgrade script to fix collation problems.
When I run this:
alter table AccountingPeriodSetDesc alter column AccountingPeriodSet collate SQL_Latin1_General_CP1_CI_AS
I get this:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'indAccountingPeriodSetDe00001' is dependent on column 'AccountingPeriodSetDesc'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN AccountingPeriodSetDesc failed because one or more objects access this column.
However, when I do the change manually in SSMS, the system is clever enough to update all the tables so the DRI exists before and after, and the change is still made.
Is there a way to programmatically do this without having to recode what all the indexes and fk's etc are ? The solution must be a stable, simple system, not a great big script that might fail because the programmer forgot about a certain type of constraint ! So ideally access to the same API that SSMS uses to make these edits seemlessly - is there such as capability ? SMO or whatever ??