Msg 5074, Level 16, State 1, Line 6 The index ‘hremployeejogtitle_idx’ is dependent on column ‘JobTitle’

Msg 5074, Level 16, State 1, Line 6 The index ‘hremployeejogtitle_idx’ is dependent on column ‘JobTitle’

Error: During altering the column type of the table in MSSQL, we get the following error:

alter table HumanResources.Employee alter column JobTitle varchar(100);

Msg 5074, Level 16, State 1, Line 6
The index 'hremployeejogtitle_idx' is dependent on column 'JobTitle'.
Msg 4922, Level 16, State 9, Line 6
ALTER TABLE ALTER COLUMN JobTitle failed because one or more objects access this column.

Solution:

1. We need to get the DDL of the indexes that has dependence present on the table.

Right click on index name –> Script index as –> Create to –> New Query Window

2. After getting DDL, Drop the index with the following command on table employees.

Dropt index index_name on schema_name.table_name
Example:
DROP index hremployeejogtitile_idx on HumanResources.Employee

3, Now Run the ALTER command

alter table HumanResources.Employee alter column JobTitle varchar(100);

4. Now recreate the index with DDL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.