May 14, 2015 at 8:17 am
Hi guys ,
Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
May 14, 2015 at 1:45 pm
orman.ionut (5/14/2015)
Hi guys ,Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
Quick question, why would you want to do this?
😎
May 14, 2015 at 1:55 pm
Sorry to give the generic answer, but - "it depends". In my mind - this mostly depends on how your organization accesses the DB resources and modifies data.
If your organization is one that uses things like stored procedures to initiate and control data access and data modification, then you're simply looking at some form of dependency tracking (start with the initial stored procedure, review what data it modifies AND what procedures it might call, then check what THOSE procedures do, etc...). There are a few tools including one from RedGate that could make that a bit easier, by highlighting the dependencies, but still - it would cut down the effort substantially..
If on the other hand, you use things like ORM and SQL embedded in procedural code, short of going through profiler and look at what SQL calls are being made, you're pretty much in the dark. Even then - it will be a LARGE task to try to decipher what the real flow is.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2015 at 6:02 am
I would probably look to Extended Events to track this. You'd probably need to capture individual statements. If you use Causality Tracking, you should be able to see the chain of events and the order that they fired in.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2015 at 8:58 pm
Take your pick... either "NO" or "With great difficulty". You should see just the ERD's for some of the procs we write. And considering that there are many root sources of data, I'd go with a simple "No" and take up trying to invent cold fusion or a true over-unity power source rather than trying to do what you ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2015 at 7:34 am
The high level data flow depends on the sequence for which the application calls the stored procedures, so perhaps ask the application team if they have a diagram. Also, running a profiler or extended event trace that captures procedure calls and input parameters will reveal a lot about how the system works.
In my own work, I typically have procedures insert into a RunLog table for which each procedure inserts a RunID and StepID or SubStepID along with things like date/time started, completed, and sometimes even record counts. A dashboard application leverages this to keep trach of processes at runtime.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2015 at 7:42 am
Eirikur Eiriksson (5/16/2015)
Still waiting for an answer to the question Why?😎
Can't possibly speak for the OP, but it sure looks like they've got a rats nest of triggers and they want to understand it. Hopefully in order to untangle it. I'm pretty sure it would be doable with extended events and causality tracking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply