If you’re getting SQL Server error 159 that reads “Must specify the table name and index name for the DROP INDEX statement“, it’s probably because you’re trying to drop an index without specifying the table name.

When we drop an index, we must specify both the index name and the table name.

To fix this issue, include both the index name and the table name in your DROP INDEX statement.

Example of Error

Here’s an example of code that produces the error:

DROP INDEX IX_Employees_HireDate;

This results in the following error message:

Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.

This error occurred because I omitted the table name when I tried to drop the index.

Solution

To fix this error, include the table name in the ALTER TABLE statement:

DROP INDEX IX_Employees_HireDate ON Employees;

Output:

Commands completed successfully.

This time it dropped the index as planned.

Be sure to replace IX_Employees_HireDate with the name of your index, and Employees with the name of the table that it belongs to.