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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])");
- 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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])");
+ 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("'");
+ }
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ 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();
+ }
+ }
+
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ protected void processChanges(Database currentModel, Database
desiredModel, List changes, CreationParameters params) throws IOException
+ {
+ if (!changes.isEmpty())
+ {
+ writeQuotationOnStatement();
+ }
+ super.processChanges(currentModel, desiredModel, changes, params);
+ }
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ 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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])");
+ 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 '[EMAIL PROTECTED]' DROP
CONSTRAINT '[EMAIL PROTECTED])");
+ 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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' drop constraint
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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"+
