db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject svn commit: r407659 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java test/org/apache/ddlutils/io/TestAlteration.java test/org/apache/ddlutils/platform/TestMSSqlPlatform.java
Date Thu, 18 May 2006 23:19:10 GMT
Author: tomdz
Date: Thu May 18 16:19:09 2006
New Revision: 407659

URL: http://svn.apache.org/viewvc?rev=407659&view=rev
Log:
Fixed/enhanced Sql Server platform

Modified:
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java?rev=407659&r1=407658&r2=407659&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java Thu May
18 16:19:09 2006
@@ -20,14 +20,27 @@
 import java.io.StringWriter;
 import java.io.Writer;
 import java.sql.Types;
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.List;
 import java.util.Map;
 
 import org.apache.ddlutils.Platform;
+import org.apache.ddlutils.alteration.AddColumnChange;
+import org.apache.ddlutils.alteration.AddPrimaryKeyChange;
+import org.apache.ddlutils.alteration.ColumnAutoIncrementChange;
+import org.apache.ddlutils.alteration.ColumnChange;
+import org.apache.ddlutils.alteration.PrimaryKeyChange;
+import org.apache.ddlutils.alteration.RemoveColumnChange;
+import org.apache.ddlutils.alteration.RemovePrimaryKeyChange;
+import org.apache.ddlutils.alteration.TableChange;
 import org.apache.ddlutils.model.Column;
 import org.apache.ddlutils.model.Database;
 import org.apache.ddlutils.model.ForeignKey;
 import org.apache.ddlutils.model.Index;
 import org.apache.ddlutils.model.Table;
+import org.apache.ddlutils.platform.CreationParameters;
 import org.apache.ddlutils.platform.SqlBuilder;
 import org.apache.ddlutils.util.Jdbc3Utils;
 
@@ -97,28 +110,22 @@
         printAlwaysSingleQuotedIdentifier(tableName);
         println(")");
         println("BEGIN");
-        println("     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)");
-        println("     DECLARE refcursor CURSOR FOR");
-        println("     select reftables.name tablename, cons.name constraintname");
-        println("      from sysobjects tables,");
-        println("           sysobjects reftables,");
-        println("           sysobjects cons,");
-        println("           sysreferences ref");
-        println("       where tables.id = ref.rkeyid");
-        println("         and cons.id = ref.constid");
-        println("         and reftables.id = ref.fkeyid");
-        print("         and tables.name = ");
+        println("  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)");
+        println("  DECLARE refcursor CURSOR FOR");
+        println("  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname");
+        println("    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid");
+        print("    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = ");
         printAlwaysSingleQuotedIdentifier(tableName);
-        println("     OPEN refcursor");
-        println("     FETCH NEXT from refcursor into @reftable, @constraintname");
-        println("     while @@FETCH_STATUS = 0");
-        println("     BEGIN");
-        println("       exec ('alter table '+@reftable+' drop constraint '+@constraintname)");
-        println("       FETCH NEXT from refcursor into @reftable, @constraintname");
-        println("     END");
-        println("     CLOSE refcursor");
-        println("     DEALLOCATE refcursor");
-        print("     DROP TABLE ");
+        println("  OPEN refcursor");
+        println("  FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+        println("  WHILE @@FETCH_STATUS = 0");
+        println("    BEGIN");
+        println("      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)");
+        println("      FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+        println("    END");
+        println("  CLOSE refcursor");
+        println("  DEALLOCATE refcursor");
+        print("  DROP TABLE ");
         printlnIdentifier(tableName);
         print("END");
         printEndOfStatement();
@@ -188,7 +195,7 @@
     {
         String constraintName = getForeignKeyName(table, foreignKey);
 
-        print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'RI' AND name = ");
+        print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = ");
         printAlwaysSingleQuotedIdentifier(constraintName);
         println(")");
         printIndent();
@@ -263,6 +270,314 @@
         print("'");
         print(identifier);
         print("'");
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void writeCopyDataStatement(Table sourceTable, Table targetTable) throws IOException
+    {
+        // Sql Server per default does not allow us to insert values explicitly into
+        // identity columns. However, we can change this behavior
+        boolean hasIdentityColumns = targetTable.getAutoIncrementColumns().length > 0;
+
+        if (hasIdentityColumns)
+        {
+            print("SET IDENTITY_INSERT ");
+            printIdentifier(getTableName(targetTable));
+            print(" ON");
+            printEndOfStatement();
+        }
+        super.writeCopyDataStatement(sourceTable, targetTable);
+        // We have to turn it off ASAP because it can be on only for one table per session
+        if (hasIdentityColumns)
+        {
+            print("SET IDENTITY_INSERT ");
+            printIdentifier(getTableName(targetTable));
+            print(" OFF");
+            printEndOfStatement();
+        }
+    }
+
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void processChanges(Database currentModel, Database desiredModel, List changes,
CreationParameters params) throws IOException
+    {
+        if (!changes.isEmpty())
+        {
+            writeQuotationOnStatement();
+        }
+        super.processChanges(currentModel, desiredModel, changes, params);
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void processTableStructureChanges(Database currentModel,
+                                                Database desiredModel,
+                                                Table    sourceTable,
+                                                Table    targetTable,
+                                                Map      parameters,
+                                                List     changes) throws IOException
+    {
+        // First we drop primary keys as necessary
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof RemovePrimaryKeyChange)
+            {
+                processChange(currentModel, desiredModel, (RemovePrimaryKeyChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof PrimaryKeyChange)
+            {
+                PrimaryKeyChange       pkChange       = (PrimaryKeyChange)change;
+                RemovePrimaryKeyChange removePkChange = new RemovePrimaryKeyChange(pkChange.getChangedTable(),
+                                                                                   pkChange.getOldPrimaryKeyColumns());
+
+                processChange(currentModel, desiredModel, removePkChange);
+                removePkChange.apply(currentModel);
+            }
+        }
+
+
+        ArrayList columnChanges = new ArrayList();
+
+        // Next we add/remove columns
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof AddColumnChange)
+            {
+                AddColumnChange addColumnChange = (AddColumnChange)change;
+
+                // Oracle can only add not insert columns
+                if (addColumnChange.isAtEnd())
+                {
+                    processChange(currentModel, desiredModel, addColumnChange);
+                    change.apply(currentModel);
+                    changeIt.remove();
+                }
+            }
+            else if (change instanceof RemoveColumnChange)
+            {
+                processChange(currentModel, desiredModel, (RemoveColumnChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof ColumnAutoIncrementChange)
+            {
+                // Sql Server has no way of adding or removing a IDENTITY constraint
+                // Thus we have to rebuild the table anyway and can ignore all the other

+                // column changes
+                columnChanges = null;
+            }
+            else if ((change instanceof ColumnChange) && (columnChanges != null))
+            {
+                // we gather all changed columns because we can use the ALTER TABLE ALTER
COLUMN
+                // statement for them
+                columnChanges.add(change);
+            }
+        }
+        if (columnChanges != null)
+        {
+            HashSet processedColumns = new HashSet();
+
+            for (Iterator changeIt = columnChanges.iterator(); changeIt.hasNext();)
+            {
+                ColumnChange change       = (ColumnChange)changeIt.next();
+                Column       sourceColumn = change.getChangedColumn();
+                Column       targetColumn = targetTable.findColumn(sourceColumn.getName(),
getPlatform().isDelimitedIdentifierModeOn());
+
+                if (!processedColumns.contains(targetColumn))
+                {
+                    processColumnChange(sourceTable, targetTable, sourceColumn, targetColumn);
+                    processedColumns.add(targetColumn);
+                }
+                changes.remove(change);
+                change.apply(currentModel);
+            }
+        }
+        // Finally we add primary keys
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof AddPrimaryKeyChange)
+            {
+                processChange(currentModel, desiredModel, (AddPrimaryKeyChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof PrimaryKeyChange)
+            {
+                PrimaryKeyChange    pkChange    = (PrimaryKeyChange)change;
+                AddPrimaryKeyChange addPkChange = new AddPrimaryKeyChange(pkChange.getChangedTable(),
+                                                                          pkChange.getNewPrimaryKeyColumns());
+
+                processChange(currentModel, desiredModel, addPkChange);
+                addPkChange.apply(currentModel);
+                changeIt.remove();
+            }
+        }
+    }
+
+
+    /**
+     * Processes the addition of a column to a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database        currentModel,
+                                 Database        desiredModel,
+                                 AddColumnChange change) throws IOException
+    {
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(change.getChangedTable()));
+        printIndent();
+        print("ADD ");
+        writeColumn(change.getChangedTable(), change.getNewColumn());
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes the removal of a column from a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database           currentModel,
+                                 Database           desiredModel,
+                                 RemoveColumnChange change) throws IOException
+    {
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(change.getChangedTable()));
+        printIndent();
+        print("DROP COLUMN ");
+        printIdentifier(getColumnName(change.getColumn()));
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes the removal of a primary key from a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database               currentModel,
+                                 Database               desiredModel,
+                                 RemovePrimaryKeyChange change) throws IOException
+    {
+        // TODO: this would be easier when named primary keys are supported
+        //       because then we can use ALTER TABLE DROP
+        String tableName = getTableName(change.getChangedTable());
+
+        println("BEGIN");
+        println("  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)");
+        println("  DECLARE refcursor CURSOR FOR");
+        println("  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname");
+        println("    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid");
+        print("    WHERE objs.xtype = 'PK' AND object_name(objs.parent_obj) = ");
+        printAlwaysSingleQuotedIdentifier(tableName);
+        println("  OPEN refcursor");
+        println("  FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+        println("  WHILE @@FETCH_STATUS = 0");
+        println("    BEGIN");
+        println("      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)");
+        println("      FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+        println("    END");
+        println("  CLOSE refcursor");
+        println("  DEALLOCATE refcursor");
+        print("END");
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes a change to a column.
+     * 
+     * @param sourceTable  The current table
+     * @param targetTable  The desired table
+     * @param sourceColumn The current column
+     * @param targetColumn The desired column
+     */
+    protected void processColumnChange(Table  sourceTable,
+                                       Table  targetTable,
+                                       Column sourceColumn,
+                                       Column targetColumn) throws IOException
+    {
+        boolean hasDefault       = sourceColumn.getParsedDefaultValue() != null;
+        boolean shallHaveDefault = targetColumn.getParsedDefaultValue() != null;
+        String  newDefault       = targetColumn.getDefaultValue();
+
+        // Sql Server does not like it if there is a default spec in the ALTER TABLE ALTER
COLUMN
+        // statement; thus we have to change the default manually
+        if (newDefault != null)
+        {
+            targetColumn.setDefaultValue(null);
+        }
+        if (hasDefault)
+        {
+            // we're dropping the old default
+            String tableName  = getTableName(sourceTable);
+            String columnName = getColumnName(sourceColumn);
+
+            println("BEGIN");
+            println("  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)");
+            println("  DECLARE refcursor CURSOR FOR");
+            println("  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname");
+            println("    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid");
+            println("    WHERE objs.xtype = 'D' AND");
+            print("          cons.colid = (SELECT colid FROM syscolumns WHERE id = object_id(");
+            printAlwaysSingleQuotedIdentifier(tableName);
+            print(") AND name = ");
+            printAlwaysSingleQuotedIdentifier(columnName);
+            println(") AND");
+            print("          object_name(objs.parent_obj) = ");
+            printAlwaysSingleQuotedIdentifier(tableName);
+            println("  OPEN refcursor");
+            println("  FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+            println("  WHILE @@FETCH_STATUS = 0");
+            println("    BEGIN");
+            println("      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)");
+            println("      FETCH NEXT FROM refcursor INTO @tablename, @constraintname");
+            println("    END");
+            println("  CLOSE refcursor");
+            println("  DEALLOCATE refcursor");
+            print("END");
+            printEndOfStatement();
+        }
+
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(sourceTable));
+        printIndent();
+        print("ALTER COLUMN ");
+        writeColumn(sourceTable, targetColumn);
+        printEndOfStatement();
+
+        if (shallHaveDefault)
+        {
+            targetColumn.setDefaultValue(newDefault);
+
+            // if the column shall have a default, then we have to add it as a constraint
+            print("ALTER TABLE ");
+            printlnIdentifier(getTableName(sourceTable));
+            printIndent();
+            print("ADD CONSTRAINT ");
+            printIdentifier(getConstraintName("DF", sourceTable, sourceColumn.getName(),
null));
+            writeColumnDefaultValueStmt(sourceTable, targetColumn);
+            print(" FOR ");
+            printIdentifier(getColumnName(sourceColumn));
+            printEndOfStatement();
+        }
     }
 
     // TODO: DROP default is done via selecting the name of the constraint for column avalue
of table toundtrip

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java?rev=407659&r1=407658&r2=407659&view=diff
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java Thu May 18 16:19:09
2006
@@ -537,7 +537,10 @@
     }
 
     /**
-     * Tests the addition of a column with a default value.
+     * Tests the addition of a column with a default value. Note that depending
+     * on whether the database supports this via a statement, this test may fail.
+     * For instance, Sql Server has a statement for this which means that the
+     * existing value in column avalue won't be changed and thus the test fails.
      */
     public void testAddColumnWithDefault()
     {

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java?rev=407659&r1=407658&r2=407659&view=diff
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java Thu May
18 16:19:09 2006
@@ -55,27 +55,20 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'coltype')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'coltype'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"coltype\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'coltype'  OPEN
refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"coltype\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"coltype\"\n"+
@@ -127,27 +120,20 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'constraints')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'constraints'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"constraints\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'constraints'
 OPEN refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"constraints\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"constraints\"\n"+
@@ -170,86 +156,65 @@
     {
         assertEqualsIgnoringWhitespaces(
             "SET quoted_identifier on;\n"+
-            "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'RI' AND name = 'testfk')\n"+
+            "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = 'testfk')\n"+
             "     ALTER TABLE \"table3\" DROP CONSTRAINT \"testfk\";\n"+
             "SET quoted_identifier on;\n"+
-            "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'RI' AND name = 'table2_FK_COL_FK_1_COL_FK_2_table1')\n"+
+            "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = 'table2_FK_COL_FK_1_COL_FK_2_table1')\n"+
             "     ALTER TABLE \"table2\" DROP CONSTRAINT \"table2_FK_COL_FK_1_COL_FK_2_table1\";\n"+
             "SET quoted_identifier on;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table3')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'table3'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"table3\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table3'  OPEN
refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"table3\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table2')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'table2'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"table2\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table2'  OPEN
refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"table2\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table1')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'table1'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"table1\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table1'  OPEN
refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"table1\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"table1\"\n"+
@@ -293,27 +258,20 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'escapedcharacters')\n"+
             "BEGIN\n"+
-            "     DECLARE @reftable nvarchar(60), @constraintname nvarchar(60)\n"+
-            "     DECLARE refcursor CURSOR FOR\n"+
-            "     select reftables.name tablename, cons.name constraintname\n"+
-            "      from sysobjects tables,\n"+
-            "           sysobjects reftables,\n"+
-            "           sysobjects cons,\n"+
-            "           sysreferences ref\n"+
-            "       where tables.id = ref.rkeyid\n"+
-            "         and cons.id = ref.constid\n"+
-            "         and reftables.id = ref.fkeyid\n"+
-            "         and tables.name = 'escapedcharacters'\n"+
-            "     OPEN refcursor\n"+
-            "     FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     while @@FETCH_STATUS = 0\n"+
-            "     BEGIN\n"+
-            "       exec ('alter table '+@reftable+' drop constraint '+@constraintname)\n"+
-            "       FETCH NEXT from refcursor into @reftable, @constraintname\n"+
-            "     END\n"+
-            "     CLOSE refcursor\n"+
-            "     DEALLOCATE refcursor\n"+
-            "     DROP TABLE \"escapedcharacters\"\n"+
+            "  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)\n"+
+            "  DECLARE refcursor CURSOR FOR\n"+
+            "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
+            "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
+            "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'escapedcharacters'
 OPEN refcursor\n"+
+            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  WHILE @@FETCH_STATUS = 0\n"+
+            "    BEGIN\n"+
+            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
+            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "    END\n"+
+            "  CLOSE refcursor\n"+
+            "  DEALLOCATE refcursor\n"+
+            "  DROP TABLE \"escapedcharacters\"\n"+
             "END;\n"+
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"escapedcharacters\"\n"+



Mime
View raw message