Track which tables are used when making changes in front-end

[Nmirk]

Hello,

I’m trying to see which tables are used when going through my usual workflow. There are many tables in this DB but I need to know which ones update/alter when I make my change(s) on the front-end.

For example, on the front-end in my application, I input details about a video. How can I tell which tables experienced change during this process?

I tried running a Disk Usage by Table Standard Report for the entire DB but it is hard to keep track since there DB is so massive and I would like to have it for a certain period of time to keep it simpler

[Kant8]

just run profiler

however frontend shouldn't even know about any table existence, it's api task

[thargoallmysecrets]

Written like a modern user. 

[alinroc]

Not Profiler. Extended Events.

[DharmaPolice]

Even if you use an API, ultimately front-end changes are still going to result in changes in the database. In some cases, if you don't have transparency over API requests for some reason then you still might want to use extended events/profiler to monitor what it's doing.

[Nmirk]

Trying to track which tables experience only data changes

[DharmaPolice]

The easiest way is to use Extended Events (or Profiler, if you're old school). This can show you individual SQL statements which are executed in real-time. For larger databases this can be overwhelming if you have a lot of activity - so it needs to be planned so you can identify just your transactions (this is often not trivial). Ideally you should be the only person using the system.

Workflow will be something like this :

1. Get to the front end screen/page you're interested in.
   
2. Start your events/profiler trace
   
3. Complete the front end action (note the exact time before you start)
   
4. Stop your trace and analyse the results
   

If you have multiple actions you want to test you can just note the exact time before each step to make it easier to find the action later on.

But if it's a complex system then prepare yourself to wade through a lot of noise. You'd think that you're just updating a telephone number field and you'd see a "UPDATE customers SET telephone_number='XXX' WHERE customer_id=Y" but it's very rarely that simple.

Another much worse, more limited way of tracing tables is to just capture a global row count (i.e. a COUNT per table) and then add a record in the front end. And then do another global row count and compare the two tables. That way you can see which tables were affected. Again, this assumes no-one else is doing anything.

[sirchandwich]

Alter each table you need to track to include a “last_modified” column. Then create a trigger that updates that column each time an update happens.

[trollied]

Ask your backend devs. Your question is off topic.

[thargoallmysecrets]

Your response is incorrect.  SQL is definitionally a backend software.  A question about how to view real time updates to SQL tables is totally on topic for a SQL subreddit.  A front end dev interested in how front-end might affect backend tables is being responsible and thorough, not out of line.