Filter columns to compare across all tables
I would like to have a global filter that lets you eliminate certain named columns across all tables. I have 5 columns that are in every single table - last update date, last update time, last update user, record status and record delete flag. I want to eliminate them from the comparisons in all tables without individually selecting each table in all the databases.
Peter Lo commented
My company is a software company. Therefore, we need to constantly test the db scripts against various versions of databases. In my db upgrade test, I used the scripts to create a V2 database and upgrade a V1 database to V2, and then use SQL Data Compare to compare reference data in these 2 databases. In our system, all tables has a column 'trans_id'. All DML operations require a new trans_id and this trans_id is obtained at runtime. Therefore, the same records being added may have attached with different trans_id. So, even the records do not have change on columns except 'trans_id', the SQL Data Compare would show these records in comparison report.
By using current version of SQL Data Compare, for each new database, I can only set filter to exclude columns at table-level manually. For each new comparison, I would need to do this for approximately 60-80 tables each time, no fun to do this. therefore,I like a future version of SQL Data Compare allows me to set a global filter to exclude the column(s)
Richard Raine commented
Dismayed that this feature has not been implemented its 2016 now and I am trialing the product.
We have the same issue... please provide the solution in the app.
(copying this from a different post, essentially what Russell S. posted, but not hard-coding dbo schema)
a relatively easy way to accomplish this would be to run the TSQL script below and modify the WHERE clause as appropriate, then save a SQL Data compare to a file, open the .sdc file and paste the xml generated by sql script anywhere inside the <TableActions> node. In my case, I want to unmap all columns that begin with "Audit"
SELECT '<value version="1" type="SelectTableEvent">
<ColumnName>' + [c].[name] + ':' + [c].[name] + '</ColumnName>
<TableName>[' + OBJECT_SCHEMA_NAME([c].[object_id]) + '].[' + OBJECT_NAME([c].[object_id]) + ']:[' + OBJECT_SCHEMA_NAME([c].[object_id]) + '].['
+ OBJECT_NAME([c].[object_id]) + ']</TableName>
</value>' AS [XML]
FROM [sys].[columns] AS [c]
WHERE OBJECT_SCHEMA_NAME([c].[object_id]) NOT IN ('dbo', 'sys')
AND [c].[name] LIKE 'audit%';
the biggest problem in doing this is when you go to deploy, the columns that are not mapped are not included in the insert statement which is causing "cannot insert null value into ..." errors. What we really need is a way to ignore certain columns on the compare, but include them on the insert statements when deploying.
Russell Speight commented
I had the same problem with 700+ tables and a column names row_version but I figure out a quick workaround. Setup your compare and save the project. Then use the below SQL script (changing column name) to generate the column deselection XML. Open your project's scp file and paste generate XML at the end of the TableActions element.
'<value version="1" type="SelectTableEvent">' + CHAR(13) + CHAR(10) +
' <action>DeselectColumn</action>' + CHAR(13) + CHAR(10) +
' <ColumnName>row_version:row_version</ColumnName>' + CHAR(13) + CHAR(10) +
'<TableName>[dbo].[' + s.table_name + ']:[dbo].[' + s.table_name + ']</TableName>' + CHAR(13) + CHAR(10) +
'</value>' + CHAR(13) + CHAR(10)
from INFORMATION_SCHEMA.TABLES as s
order by s.TABLE_NAME
Any way http://redgate.uservoice.com/forums/147879-sql-data-compare-feature-suggestions/suggestions/3057690-allow-the-exclusion-of-columns-that-appear-in-mult?tracking_code=f5944cb19dc20b6ca3749bc04e45ba44 can be tied to this. It is basically the same request, and tie the votes together?
Steven Aertssen commented
Here the same, we have in each table the following columns:
creationDate, creationBy, updateDate, updateBy, deletionDate, deletionBy.
These fields, should never be compared.
Yes! All our tables have audit columns for a date created and date last updated as well as userid of person who created and updated the data last. We do not want to compare on these columns, and they are consistently named throughout the database.
Alan Gruver commented
I was just about to enter this, in my case we have 600 setup tables, each with a LastMod column.