0

I have a transactional db, a table T1 with a PK and I want to track all the changes that occurred in all tables related to T1. I thought of using CDC, but to be able to browse all the existing cdc tables to see which ones have changes related to T1 proved to be a bit of a challenge. it is very easy to get the changes from the tables that are connected to T1 directly, no matter the level (e.g. TN has a FK to TN-1, which has a FK to TN-2 etc which has a FK to T2 which has a FK to T1). But I don't know how to create a script to get the changes from the tables that are not connected directly, e.g. T6 has a FK to T5 which has a FK to T4, T3 has a FK to T4 and to T2, and T2 has a FK to T1 (1st or through multiple levels), so T6 connects to T1 in the end, but not through a one way type of relationship.

Does anyone have any idea on how to do this?

A specific example: I have a table Contracts that has a column ContractId and some other columns and I want to create a history table that holds all the changes related to a ContractId, so it would have columns ContractId, OldValue, NewValue. These changes can come from a) tables that have a FK to Contracts.ContractId, for example tblPeriod(PeriodId, ContractId, etc), b) from tables that reference Contracts.ContractId through multiple relationships, e.g. tblSubperiod (SubperiodId, PeriodId, etc) references tblPeriod which references Contracts.ContractId or c) from tables that can't be connected to Contracts only through FK keys, e.g. tblComissionShare has a FK to tblCommission, tblSubperiod has a FK to tblCommission and as we know from above, tblSubperiod has FK to tblPeriod which has FK to Contracts. So any change in table tblCommissionShare should appear in my history table. For querying the CDC tables I would need a dynamic query that generates joins between table Contracts and all the other tables that relate to it as described above so I can populate my history table.

alina
  • 3
  • 4
  • Possible duplicate of [How can I list all foreign keys referencing a given table in SQL Server?](http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) – David Rushton May 09 '16 at 10:05
  • no, my question is about smth more complex than listing FKs for a table – alina May 09 '16 at 11:52
  • Your meaning is unclear where you say "all changes related to T1", "get the changes from the tables", ... . – Erwin Smout May 09 '16 at 13:22
  • thanks for pointing this out - I modified the description of my question, hope it makes more sense now – alina May 09 '16 at 13:53

0 Answers0