Change datatype of a column
-
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/ -
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
-
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/ -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just wanted to share that I burped a while ago
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply