Support schemas for MSSQL drop server default + FK constraint

Fixed bug in MSSQL dialect where the drop constraint execution steps used
to remove server default or implicit foreign key constraint failed to take
into account the schema name of the target table.

Change-Id: Ia95b043ee6289efbb90d6f21392f4ce622748611
Fixes: #621

1 files changed, 45 insertions, 0 deletions

diff --git a/tests/test_mssql.py b/tests/test_mssql.py
index 9418271..28a0bd9 100644
--- a/tests/test_mssql.py
+++ b/tests/test_mssql.py
@@ -117,9 +117,28 @@ class OpTest(TestBase):
         context = op_fixture("mssql")
         op.alter_column("t", "c", server_default=None)
         context.assert_contains(
+            "declare @const_name varchar(256)select @const_name = [name] "
+            "from sys.default_constraintswhere parent_object_id = "
+            "object_id('t')and col_name(parent_object_id, "
+            "parent_column_id) = 'c'"
+        )
+        context.assert_contains(
             "exec('alter table t drop constraint ' + @const_name)"
         )
 
+    def test_alter_column_drop_default_w_schema(self):
+        context = op_fixture("mssql")
+        op.alter_column("t", "c", server_default=None, schema="xyz")
+        context.assert_contains(
+            "declare @const_name varchar(256)select @const_name = [name] "
+            "from sys.default_constraintswhere parent_object_id = "
+            "object_id('xyz.t')and col_name(parent_object_id, "
+            "parent_column_id) = 'c'"
+        )
+        context.assert_contains(
+            "exec('alter table xyz.t drop constraint ' + @const_name)"
+        )
+
     def test_alter_column_dont_drop_default(self):
         context = op_fixture("mssql")
         op.alter_column("t", "c", server_default=False)
@@ -166,10 +185,36 @@ class OpTest(TestBase):
         context = op_fixture("mssql")
         op.drop_column("t1", "c1", mssql_drop_foreign_key=True)
         context.assert_contains(
+            "declare @const_name varchar(256)\n"
+            "select @const_name = [name] from\n"
+            "sys.foreign_keys fk join sys.foreign_key_columns fkcon "
+            "fk.object_id=fkc.constraint_object_id\n"
+            "where fkc.parent_object_id = object_id('t1')`and "
+            "col_name(fkc.parent_object_id, fkc.parent_column_id) = 'c1'\n"
+            "exec('alter table t1 drop constraint ' + @const_name)"
+        )
+        context.assert_contains(
             "exec('alter table t1 drop constraint ' + @const_name)"
         )
         context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
 
+    def test_drop_column_w_fk_schema(self):
+        context = op_fixture("mssql")
+        op.drop_column("t1", "c1", schema="xyz", mssql_drop_foreign_key=True)
+        context.assert_contains(
+            "declare @const_name varchar(256)\n"
+            "select @const_name = [name] from\n"
+            "sys.foreign_keys fk join sys.foreign_key_columns fkcon "
+            "fk.object_id=fkc.constraint_object_id\n"
+            "where fkc.parent_object_id = object_id('xyz.t1')`and "
+            "col_name(fkc.parent_object_id, fkc.parent_column_id) = 'c1'\n"
+            "exec('alter table xyz.t1 drop constraint ' + @const_name)"
+        )
+        context.assert_contains(
+            "exec('alter table xyz.t1 drop constraint ' + @const_name)"
+        )
+        context.assert_contains("ALTER TABLE xyz.t1 DROP COLUMN c1")
+
     def test_drop_column_w_fk_in_batch(self):
         context = op_fixture("mssql")
         with op.batch_alter_table("t1", schema=None) as batch_op: