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.
- I have a source of data in a custom app that is for most part of the time the result of a stored procedure.
- 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.
- 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):
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...