Link to home
Start Free TrialLog in
Avatar of ghassan99
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
ASKER CERTIFIED SOLUTION
Avatar of SashP
SashP

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ShogunWade
ShogunWade

sash is correct.     SQL Enterprise mrg basically scripts up the constraints (on the quiet) and drops them, applies the change and recreates the constraints.

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
Avatar of ghassan99

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




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
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 ?