Monday, April 14, 2014

Auditing MS SQL Dependencies


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.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.



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.

No comments:

Post a Comment

Please only include comments that add to the substance of the Blog Post:
- Question or request for clarification
- Idea for improvement
- Identifying an issue

Please refrain from promotion.