Alter column from datetimeoffset to datetime when index is dependent on it.

Question

Alter column from datetimeoffset to datetime when index is dependent on it.

chalam87

Right there with Babe

Points: 771
More actions
February 5, 2016 at 12:40 am

#308290

Hi,
i am not able to alter column from datetimeoffset to datetime, when index is dependent on alter column.
Msg 5074, Level 16, State 1, Line 1
The index 'TestsSampleIdCoveringIndex' is dependent on column 'ReceivedDateTime'.
Msg 5074, Level 16, State 1, Line 1
The index 'IDX_Tests_ReceivedDateTime' is dependent on column 'ReceivedDateTime'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ReceivedDateTime failed because one or more objects access this column.
Pls tell me how can i alter column which is used in index. i need to alter column in all databases,in some database tables have index's other db's doesn't have.
--chalam

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

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

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

Drop the indexes, alter the column, recreate the indexes

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

chalam87 Right there with Babe Points: 771 More actions · Answer 2

Hi,

Is there any other solution except drop and recreate index.

while recreate index i got below error

Msg 7999, Level 16, State 9, Line 1

Could not find any index named 'xxxxxxxx' for table 'xxxxxxxxx'.

--chalam

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

You need to drop the index, then alter the table, then create the index again. You can't alter a column with an index on it

That error is probably because you have WITH DROP_EXISTING in the options of the create index. That requires that the index already exists, which if you dropped it earlier won't be the case.

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

chalam87 Right there with Babe Points: 771 More actions · Answer 4

For statistics also same procedure?. if same procedure is there any script to get create statistics script dynamically based on table and column name.

--chalam