I am comparing all tables in two databases for the sole purpose of automating bulk copying the data from source to target.
I want to turn off comparisons involving the following :
a) computed columns - no data invovled, don't care if present on both and different, or if present only on one side, I don't want to see a comparison differences hit on computed columns at all.
Some computed column definitions may affect target table's ability select data (divide by zero errors, etc.) but are case-by-case.
Definitely need option to exclude as most times my target tables are not created with the computed columns at all when present in source tables.
b) constraints - all records are copied with values present so defaults are irrelevant; other constraints when present may affect target table's ability to receive inserted data, but should have option to exclude when it is known that constraints were not created on target database
provided - ignore xtype 'C' 'Constraint(Check)'
missing - ingore xtype 'D' 'Constraint(Default)'
provided - ignore xtype 'F' 'Constraint(Foreign Key)'
missing - ignore xtype 'PK' 'Constraint(Primary Key)'
missing - ignore xtype 'UQ' 'Constraint(Unique)'
I note that all the constraint types are mentioned in the mouseover description for the option 'Ignore Contraint and index names' but not all types of contraints are currenly able to be ignored.
Tables created from DML such as 'SELECT f1, f2,.. FROM DB1.dbo.tableA INTO DB2.dbo.tableA WHERE 1=0' will create table enough definition to contain data but won't create all constraints such as Defaults on target table.
c) NULLability - target tables may be created to accept NULLs on all fields for ease of import and export
Basically without being able to ignore comparisons based on the list above, I have to manually check hundreds of tables for these "false positives" that are irrelevant to the task at hand.
I will probably try out competitors' products to see if this functionality is included, I'd be happy to report back if anyone cares.
TIA,
sqlbug
Hi there,
I want to turn off comparisons involving the following :
a) computed columns - no data invovled, don't care if present on both and different, or if present only on one side, I don't want to see a comparison differences hit on computed columns at all.
Some computed column definitions may affect target table's ability select data (divide by zero errors, etc.) but are case-by-case.
Definitely need option to exclude as most times my target tables are not created with the computed columns at all when present in source tables.
b) constraints - all records are copied with values present so defaults are irrelevant; other constraints when present may affect target table's ability to receive inserted data, but should have option to exclude when it is known that constraints were not created on target database
provided - ignore xtype 'C' 'Constraint(Check)'
missing - ingore xtype 'D' 'Constraint(Default)'
provided - ignore xtype 'F' 'Constraint(Foreign Key)'
missing - ignore xtype 'PK' 'Constraint(Primary Key)'
missing - ignore xtype 'UQ' 'Constraint(Unique)'
I note that all the constraint types are mentioned in the mouseover description for the option 'Ignore Contraint and index names' but not all types of contraints are currenly able to be ignored.
Tables created from DML such as 'SELECT f1, f2,.. FROM DB1.dbo.tableA INTO DB2.dbo.tableA WHERE 1=0' will create table enough definition to contain data but won't create all constraints such as Defaults on target table.
c) NULLability - target tables may be created to accept NULLs on all fields for ease of import and export
Basically without being able to ignore comparisons based on the list above, I have to manually check hundreds of tables for these "false positives" that are irrelevant to the task at hand.
I will probably try out competitors' products to see if this functionality is included, I'd be happy to report back if anyone cares.
TIA,
sqlbug