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
LVL 6
ghassan99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
There is no way to disable the constraint.  You need to drop it then re-create it.

If you want the code to drop and recreate all your scripts.

You do not need to code it as enterprise manager does it for you.

Right click on the database and select Generate SQL Script.  Select all tables and the options to generate drop and generate constraints.

It will generate all the script to drop all the constrints tables and recreate them.  Trim out all the table drop and create code and insert you alter table script.

Cheers Sash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShogunWadeCommented:
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
0
ghassan99Author Commented:
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




0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SashPCommented:
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
0
ShogunWadeCommented:
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.
0
ShogunWadeCommented:
Do you need any more assistance with this question ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.