How to fix Error 159 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 159 — “Must Specify the Table Name and Index Name for the DROP INDEX Statement”

Why This Error Happens

This error occurs because SQL Server 2025 enforces stricter parsing rules for the DROP INDEX statement, requiring the fully qualified format DROP INDEX index_name ON table_name rather than the older, more permissive syntax. Scripts or stored procedures migrated from legacy SQL Server versions (or generated by outdated tools) often omit the ON table_name clause, causing the parser to reject the command. It can also surface when database migration scripts, ORM-generated DDL, or third-party deployment tools have not been updated to comply with the newer T-SQL syntax standards enforced in this release.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact DROP INDEX statement causing Error 159.
  • Check the SQL Server Error Log (via EXEC sp_readerrorlog; or Event Viewer under Applications and Services Logs > MSSQLSERVER) to confirm the exact statement and line number.
  • Note the index name and its associated table from your database schema documentation or by running:
  SELECT i.name AS IndexName, t.name AS TableName
  FROM sys.indexes i
  JOIN sys.tables t ON i.object_id = t.object_id
  WHERE i.name = 'YourIndexName';

Step 2: Correct the DROP INDEX Syntax

  • Rewrite the statement using the mandatory SQL Server 2025 syntax format:
  DROP INDEX IX_YourIndexName ON dbo.YourTableName;
  • If dropping multiple indexes across tables, use one DROP INDEX ... ON ... clause per index, separated by commas:
  DROP INDEX IX_Index1 ON dbo.Table1,
             IX_Index2 ON dbo.Table2;
  • Avoid using the deprecated shorthand DROP INDEX TableName.IndexName, which is no longer supported in this version.

Step 3: Update Migration Scripts and Automation Tools

  • Search all deployment/migration .sql files (typically located in C:\Program Files\Microsoft SQL Server\160\Tools\Scripts\ or your custom deployment repo) for legacy DROP INDEX calls.
  • Update any Entity Framework, SSDT (SQL Server Data Tools), or third-party schema comparison tool templates to generate the corrected syntax.
  • Re-run the corrected script in a test environment first using SSMS’s Query > SQLCMD Mode to catch any remaining syntax issues before deploying to production.

Step 4: Apply and Redeploy

  • Save the corrected script and redeploy it through your standard change management process (e.g., DevOps pipeline, SSMS execution, or Agent Job).
  • If using SQL Server Agent Jobs, edit the job step under SQL Server Agent > Jobs > [Job Name] > Steps and paste the updated command syntax.

Alternative or Verification Steps

  • Verify the fix by re-executing the corrected DROP INDEX statement and confirming no Error 159 appears in the Messages tab of SSMS or in the SQL Server Error Log.
  • As an alternative, use SSMS’s GUI: right-click the index under Object Explorer > Databases > YourDB > Tables > YourTable > Indexes, select Delete, and let SSMS auto-generate the correctly formatted T-SQL script for future reuse.