How can I trace back the original table of a column?

Question

New to a software engeneer job and I am kinda blocked on the way to go with my new project To explain this in a scheme.

1. I have a source of data in a custom app that is for most part of the time the result of a stored procedure.
2. Here is a path (Fictional used as an example) of the path that the data had to do before going to the Data Source CODPOT.
3. Here is a representation (Fictional used as an example) of what the table of origin of the data for CODPOT can be.

So, the goal of the project being to be able in the program and this dynamically (So applicable for all sources and this regardless of the model, the original table or the type of data) to get it's original table to allow analysts to more easily verify and correlate data. That way they won't need to recreate the complete life path of a data, because manually it can sometime take up to 3 days to retrace. The thing is most part of the time, the stored procedure uses OPENQUERY to get data from distant servers, and ever in these servers, there can be views before finding the original table. So if we want put this in the scheme, at step 2 most part of the time one the square is an OPENQUERY to an other server.

What can I do to be able to trace back the original table, can anyone guide me to the right path ? Because frankly I have no idea how I could do it. I've already looked up for INFORMATION_SCHEMA, ORIGINAL_DB_NAME and I am not sure if "sp_describe_first_result_set" will be working for this. I've already looked all of these (And more, but these are the mostconsistent with what I want):

• https://chartio.com/learn/databases/using-information-schema-views-to-check-to-see-if-table-exists-in-sql-server/¸
• https://stackoverflow.com/questions/43289215/use-tsql-to-find-the-original-table-that-a-column-within-a-view-came-from
• https://www.kodyaz.com/t-sql/find-sql-server-views-a-table-is-used-list-tables-in-a-database-view.aspx#:~:text=To%20find%20all%20of%20the,VIEW_TABLE_USAGE%20as%20seen%20below
• https://blog.devart.com/sql-server-query-history.html
• https://database.guide/how-original_db_name-works-in-sql-server/
• https://github.com/mkleehammer/pyodbc/pull/765

Maybe there is some sort of history in SQL Management that I can exploit to get this information or something like this. Thx for your time !

EDIT: Now that I've found that what I wanna do is Data Lineage of a SQL Server,to clarify I am looking for advice/guidance on how I can do this, what language should I use to create the tool, will I need to create multiple tool, where to start to create the tool, what will I need for the tool (History table, metadata, etc...), If I need metadata, how can I access multiple layer of metadata. Maybe some advice of already existing tool etc...

Answer 1

If you're hopping between engines and moving through many layers of queries and views, then it's very unlikely you'll find an automated solution that can trace the dependencies.

I seem to remember reading a while ago that Google (or whoever) was doing some research on this, so it's not a solved problem.

You'll also find that whilst it might make sense in simple cases to think of data "coming from" a specific place, once you start doing anything more than simple fetching of data and start doing any real processing, then simply compiling dependencies in a mechanical way might produce a wildly long and uninformative list.

Typically you'd have to have an idea of your purpose in performing the tracing, and then analyse all the processing logic in detail to decide whether a particular dependency is a relevant one or not. The number of actual dependencies - things that can theoretically determine or alter the output - is often much, much larger than the number of dependencies which are relevant to a particular question or investigation.