At this point, all I've given you is a set of tables that can store metadata about database tables and their relationships - they pretty much reflect a subset of what you can get from the system views. Let's take a look at the process for capturing this information as the database is modified. The following are the major steps:
After these steps are done any change to any table or relationship will result in a new version of the information being stored in the Entity Tracker database. Over time, this will allow us to monitor how the data model has changed for a particular database.
Attached is a zip that contains the SQL needed to create the Entity Tracker database tables, UpdateSchemaRelations stored procedure, and UpdateSchemaVersion DDL trigger. Let's take a look at each step and some of the code.
1) Create the EntityTracker database - That's actually not in the zip (CREATE DATABASE ENTITYTRACKER is all you need or use SSMS). You might want to call it something else, that's not a problem, but you'll need to modify the UpdateSchemaRelations stored procedure in that case to match. The reason is that this stored procedure is actually implemented into the target database in addition to the DDL trigger. The reason is that the stored proc references system tables from the database being monitored and I haven't figured out a way to get the stored proc to be able to do that aside from putting it into the target DB (if somebody else knows a way, please comment).
2) Next, the change log and supporting tables (the ones from the diagram in the introductory post) need to be created along with the view to facilitate querying relationships. The script EntityTracker.Tables.sql and EntityTracker.Views.sql in the EntityTrackerCore.zip handles this. Note, we only care about the view_SchemaTableRelations view at this point, you can disregard the other views for now.
3) Create supporting stored procedure (UpdateSchemaRelations) for DDL Trigger to invoke and supporting user-defined function. These files are named UpdateSchemaRelations.Procedure.sql and the function is found in EntityTracker.Functions.sql (We are only interested in the dbo.udf_GetTableMaxDepth function in the EntityTracker.Functions.zip). This is the core logic that actually makes the snapshot of the data. Where possible, the procedure uses standard Information Schema views with the exception of the relationships snapshot (had to use Sys.Indexes for retrieving key info due to problem with unique index not included as table constraints when the primary columns are already defined as a separate primary key).
Before getting buried in the code, let's look at the high-level logic:
For the next posts, we'll drill down into the specific .SQL code and finally we will look at the user interface in more detail. I'll also make a live demo available that you can use to get an appreciation of the user experience. After that, we'll go back down into the code used to support the UI and discuss extending this technique for other scenarios.