Change datatype of a column

Question

Change datatype of a column

kapil_kk

SSC-Insane

Points: 21316
More actions
March 2, 2014 at 10:53 pm

#304690

Hello all,
I have to change the datatype of a column in all tables of a database where that column exists.
For this I disable the constraint of all the tables runing this query:
EXEC sp_msforeachtable 'Alter table ? NOCHECK Constraint ALL'
After this when I run the query to change datatype of column I am getting error like:
ALTER TABLE ALTER COLUMN column1failed because one or more objects access this column.
The object 'uq1_Building' is dependent on column 'column1'.
What should I do to accomplish this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Check for FK relationships, I believe you have to drop the FK constraints first, then try what you were doing.

Try running sp_help on the tables to see what is tied to it...this will let you know if FK's, user created statistics, or schema-binded views reference the column.

Also, make sure no indexes exist using that column, while I'm not 100% sure on this, it could also be causing your issue.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

yes I have unique contraint , nonclusted index exists on tables might be they are causing this issue....

But I dont have any idea that can we disable uniquest constraint or not....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You need to drop all constraints, check constraints, foreign key, unique constraints, not disable them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AdityaSingh SSC Journeyman Points: 89 More actions · Answer 4

Just wanted to share that I burped a while ago 😀