From 301198f3b2d8dd45a0dd8a939bb5cbedc5f6f002 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Edgar=20Ram=C3=ADrez=20Mondrag=C3=B3n?= Date: Thu, 18 Dec 2025 14:58:59 -0500 Subject: [PATCH] Support for `IF EXISTS` in SQL Server 2016 (13.x) and later versions MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Added support for the ``IF EXISTS`` clause when dropping indexes on SQL Server 2016 (13.x) and later versions. The :paramref:`.DropIndex.if_exists` parameter is now honored by the SQL Server dialect, allowing conditional index drops that will not raise an error if the index does not exist. Pull request courtesy Edgar Ramírez Mondragón. Fixes: #13045 Closes: #13046 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13046 Pull-request-sha: 65dca2055cb403430730b5cf42f0c5f55b23bfb1 Change-Id: Iab95b1a46003b38709a791b8a7c4233dfda5e830 (cherry picked from commit 40fc3c90e1fee3f7a19184ab57cca3cbcdfa6da1) --- doc/build/changelog/unreleased_20/13045.rst | 9 +++++++++ lib/sqlalchemy/dialects/mssql/base.py | 12 ++++++++---- test/dialect/mssql/test_compiler.py | 8 ++++++++ 3 files changed, 25 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/13045.rst diff --git a/doc/build/changelog/unreleased_20/13045.rst b/doc/build/changelog/unreleased_20/13045.rst new file mode 100644 index 0000000000..9ac8257451 --- /dev/null +++ b/doc/build/changelog/unreleased_20/13045.rst @@ -0,0 +1,9 @@ +.. change:: + :tags: usecase, mssql + :tickets: 13045 + + Added support for the ``IF EXISTS`` clause when dropping indexes on SQL + Server 2016 (13.x) and later versions. The :paramref:`.DropIndex.if_exists` + parameter is now honored by the SQL Server dialect, allowing conditional + index drops that will not raise an error if the index does not exist. + Pull request courtesy Edgar Ramírez Mondragón. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index d422ad8c3d..148a4f83e1 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -984,6 +984,7 @@ import codecs import datetime import operator import re +from typing import Any from typing import overload from typing import TYPE_CHECKING from uuid import UUID as _python_UUID @@ -1034,6 +1035,7 @@ from ...util import update_wrapper from ...util.typing import Literal if TYPE_CHECKING: + from ...sql.ddl import DropIndex from ...sql.dml import DMLState from ...sql.selectable import TableClause @@ -2700,11 +2702,13 @@ class MSDDLCompiler(compiler.DDLCompiler): return text - def visit_drop_index(self, drop, **kw): - return "\nDROP INDEX %s ON %s" % ( - self._prepared_index_name(drop.element, include_schema=False), - self.preparer.format_table(drop.element.table), + def visit_drop_index(self, drop: DropIndex, **kw: Any) -> str: + index_name = self._prepared_index_name( + drop.element, include_schema=False ) + table_name = self.preparer.format_table(drop.element.table) + if_exists = " IF EXISTS" if drop.if_exists else "" + return f"\nDROP INDEX{if_exists} {index_name} ON {table_name}" def visit_primary_key_constraint(self, constraint, **kw): if len(constraint) == 0: diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index eb4dba0a07..8de8a21854 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -1532,6 +1532,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "DROP INDEX idx_foo ON bar.foo", ) + def test_drop_index_if_exists(self): + m = MetaData() + t1 = Table("foo", m, Column("x", Integer), schema="bar") + self.assert_compile( + schema.DropIndex(Index("idx_foo", t1.c.x), if_exists=True), + "DROP INDEX IF EXISTS idx_foo ON bar.foo", + ) + def test_index_extra_include_1(self): metadata = MetaData() tbl = Table( -- 2.47.3