Auditing MS SQL Dependencies

Josh,

So one of the concepts discussed at the Kimball training was around audit metadata and tracking the objects and actions that get a Dimension or Fact to its final result. This definitely seems like it could be valuable but also could require a lot of effort.  Unless it is a firm business requirement, it isn't likely to be implemented due to the large amount of time and effort to track and document the lineage.

However, some of the tracking might be already done for you!

So, while going through Stack Overflow, you found this answer that points to some built in table valued functions:

sys.dm_sql_referencing_entities

SELECT
   r.*,
   o.name,
   o.type_desc
FROM
   sys.dm_sql_referencing_entities('dbo.Contacts', 'OBJECT') r
   INNER JOIN
   sys.objects o ON r.referencing_id = o.OBJECT_ID  

This query returns back a list of the objects that reference the 'dbo.Contacts' table, so you can see which stored procedures or other objects rely on or do work on this table.

sys.dm_sql_referenced_entities

SELECT
   *FROM
   sys.dm_sql_referenced_entities('dbo.MyProcedure', 'OBJECT') 

This query returns back all the objects that are referenced by 'dbo.MyProcedure', so you can see which tables (or other objects) are touched by this stored procedure.  It even indicates if the dependency is a select or update!

For further investigation, there appears to be some open source tools for reading the metadata from the Microsoft BI stack.