October 30, 2024 at 8:49 am
I have a couple of databases
One would be regarded as the source and another as the destination
The destination is kind of a subset of source having gone through an ETL process
Although the E part is scripted access as both databases reside on the same server
So, there are a number of stored procs that basically access source data and manipulate it before writing to destination tables
Is there an easy way to see what source data (tables and columns) is actually read here?
The (very time consuming) alternative would be to go through every stored proc and note where a table/column is referenced
Thanks
- Damian
October 30, 2024 at 11:44 am
An easy way? No.
There just isn't a mechanism that shows, this column, or this table was read. Instead, you can capture query metrics using Extended Events and filter that on, for stored procedures, only those that access your given table for rpc_completed, or, for batches, filtering on that table in the WHERE clause of the Extended Event for batches, sql_batch_completed. It's not pretty. It will generate tons of data, depending on the load on your system, but it's the only way I know to see "What queries were run against MyTable." To add columns, just specify those in the WHERE clause of the Extended Event as well.
"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
October 30, 2024 at 1:14 pm
Thanks Grant.
Yes, had a feeling that might be the case
It's an exercise I haven't done for some time so it's always worth finding out if there's something new out there.
Oh well, at least I can plan for this been a manual task
Extended events will probably prove to be more accurate and easier
As you say, we can filter by table and work through on a table by table basis
Easier that SP by SP as some of our SPs are big, complex and hit a lot of tables
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply