In my database I have a table that is used to store document references - namely the source of the data being captured in all other tables. I call this table "InfoSource". Thus each table in the database may have multiple foreign key references to an entry in "InfoSource".
For instance, in a table called "person" I may have the following fields: "birthday", "weight", "height", "favorite color". For each field I would have an additional field "birthday_infosource", "weight_infosource" etc. that is a foreign key to an entry in "InfoSource".
Now I would like to create a cross reference. Given a record in "InfoSource" which records across all other tables point to it?
Right now the way I do this is via a query with a bunch of unions, where I query each individual table. The drawback is that every time a new table is added or a table is changed, I have to update the cross-reference query.
Does it make sense to implement this via a stored procedure? In pseudo code I can think of something like this:
for each table
for each field with "InfoSource" in name"
select record from InfoSource referenced by foreign key field
Unfortunately, I have no idea how I'd begin to even implement this in a SP. I'm open to suggestions.