ghassan99
asked on
Disabling constraint check
Hi Experts,
I'm trying to change the collation of many fields in the database. I'm using Alter table...collate
I have generated a long script to change all the fields of type char and varchar in all the tables, there are thousands of fields.
When I start the script I get this error
The index abc is dependent on column xyz
failed because one or more objects access this column
When I change the collation via Enterprise manager on the same field or column I dont get an error message and the change is successful.
I cannot drop the constraint on each column via script, coz as I said earlier I have thousands of fields.
Is there a way to stop SQL server constraint check
Thanks for any feedback,
-Gus
I'm trying to change the collation of many fields in the database. I'm using Alter table...collate
I have generated a long script to change all the fields of type char and varchar in all the tables, there are thousands of fields.
When I start the script I get this error
The index abc is dependent on column xyz
failed because one or more objects access this column
When I change the collation via Enterprise manager on the same field or column I dont get an error message and the change is successful.
I cannot drop the constraint on each column via script, coz as I said earlier I have thousands of fields.
Is there a way to stop SQL server constraint check
Thanks for any feedback,
-Gus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One reason I wont go this route is becauase I have huge data in the database. I cant drop the tables.
I know there is one command to stop/disable SQL checking on the constraints:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
My problem really lies with the indexes, I need to drop the indexes and re-create them after the alter.
-Gus
I know there is one command to stop/disable SQL checking on the constraints:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
My problem really lies with the indexes, I need to drop the indexes and re-create them after the alter.
-Gus
As far as I am aware
You can not change the collation of a column that has a check constraint regardless of whether the check constraint has been disabled or not.
By disabling the check constraint you prevent the check constraint from checking data inserts updates etc. but you will still encounter the errors
Server: Msg 5074, Level 16, State 6, Line x
The object '[Constraint Name]' is dependent on column '[Column Name].
Server: Msg 4922, Level 16, State 1, Line x
ALTER TABLE ALTER COLUMN [Column Name] failed because one or more objects access this column.
I am not aware of a method that will allow you to change the collation on the live database without dropping the objects that reference the column, including your constraints and indexes.
Sash
You can not change the collation of a column that has a check constraint regardless of whether the check constraint has been disabled or not.
By disabling the check constraint you prevent the check constraint from checking data inserts updates etc. but you will still encounter the errors
Server: Msg 5074, Level 16, State 6, Line x
The object '[Constraint Name]' is dependent on column '[Column Name].
Server: Msg 4922, Level 16, State 1, Line x
ALTER TABLE ALTER COLUMN [Column Name] failed because one or more objects access this column.
I am not aware of a method that will allow you to change the collation on the live database without dropping the objects that reference the column, including your constraints and indexes.
Sash
Depending on the To & From collation it is possible to simply modify the system tables directly, I know of a number of peaople that have done this. However I do not ensorse this approach as it is extremely dangerous.
Do you need any more assistance with this question ?
If you wanted to simulate this behaviour you would need to refernce SQL DMO (this is what enterprise manager does) or write lots and lots and lots of tsql