You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by to...@apache.org on 2006/05/19 01:19:10 UTC
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
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"+