0

I'm in a Data Warehouse environment where tables are generated by stored procedures, the stored procedures reference other tables, and those referenced tables are created by other stored procedures, and so on etc. I'd like to, for a given procedure or table, be able to trace back all the way to the source tables, effectively constructing a dependency chain for the given object.

I can use sys.dm_sql_referenced_entities for procedures and sys.dm_sql_referencing_entities for tables but these only reveal the first link in the chain. Presumably this is some kind of recursive query that stops when it finds no more dependencies? Has anyone done this before and can share how to do it?

Thank you.

Oaty
  • 151
  • 1
  • 15
  • It does the whole chain but you need to connect it yourself. For example, it says A is linked to B in one record and it also says B is linked to C in another record. Of course it doesn't give you the direction that the data flows, but you can derive that if your system is consistently built – Nick.Mc Jun 27 '22 at 04:38
  • Are you trying to visualise it, or are you trying to just run a report, and do you care about data direction? Because it will tell you all the objects that a stored procedure references but doesn't tell you if it is selecting from them or inserting into them – Nick.Mc Jun 27 '22 at 05:00
  • Not to visualize, but to ascertain what its associated with all the way back to source. The purpose would be for change management, e.g., we have some analytics that consume tables and would like to find out everything in our sql server environment they're dependent on, and the things they're dependent on, etc. In this context the direction is back to source, however it would be useful forwards but that could be another question. – Oaty Jun 27 '22 at 05:11
  • It's a very common challenge. There are many third party tools that do this, like informatica for example ($$$). Apparently the latest version of Purview also does it but I haven't tried it and this may not be practical . Again keep in mind there is no "direction" in those dml views, only a non directional connection, so you need to work that out yourself. Basically there are any number of T-SQL algorithms that work out dependencies based on a self referencing graph.. let me see if I caan find one.. – Nick.Mc Jun 27 '22 at 05:30
  • 1
    Yeah here's an example https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cad9109a-9cd3-46bf-aa4b-1a37204dfacd/recursive-cte-invocation-of-sysdmsqlreferencedentities-as-a-udf-works-for-views-and-udfs-but?forum=transactsql – Nick.Mc Jun 27 '22 at 05:35
  • 1
    What you're talking about is processing a _graph_ (a bunch of linked nodes). You could also load this data into SQL Server graph tables and use graph processing to simplify this. – Nick.Mc Jun 27 '22 at 05:37
  • These system view won't tell you who *created* the table, only inserts or selects https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f47a909cc0f9604181ee7ac7310e6daf – Charlieface Jun 27 '22 at 13:22
  • inserts or selects, _or updates or deletes, truncates etc._, but doesn't distinguish between them – Nick.Mc Jun 28 '22 at 05:44
  • @Nick.McDermaid As far as DDL, it does not show `CREATE` `ALTER` `DROP` only `TRUNCATE` see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4c2ac92a56378b92eb0c9a7aa4f9b425 – Charlieface Jun 28 '22 at 11:10

0 Answers0