asked
Would like to know how a table is populated
Good morning! I am using TSQL, there is one table that was created years back by another empolyee, I am not sure how this table is populated? I checked the dependencies and it just gives the name of table. I want to know how the columns/data get populated in that table for example. "Freight.load" is the table name. i have ssms 2008 Thank you in advance!
@sukichweet , Any feedback on any of the suggestions below? This IS a two way street! ;)
The only way that you can find this out will be to run profiler to monitor for the execution of this stored procedure. Unfortunately profiler is both intrusive and will eat up disk space. So you will have to create a job to execute sp_trace_create which will run profiler. Create your job to execute a few minutes before the SP executes. This houkld show you where the SP is called from. A good link to explain how to do this is:
http://www.handsonsqlserver.com/how-to-create-a-sql-server-trace-file-schedule-it-to-run-and-analyze-the-trace-file-results/
If the criteria for the trace includes the table name (it would appear that the OP doesn't know if it's a stored procedure populating the table or not), then columns to return the host, login used, and a wealth of other information can be included to help isolate who/what is populating the table.
Additionally, I would set a filter for only that table in question. This would eliminate everything else that would show up in the profiler, and only things impacting this table. Here is a good link for using SQL Profiler I think:
https://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/ To find what the OP is looking for will require work, and research I am hoping that I was able to steer him in the right direction. Personally, I don't like to give the answers outright but be able to provide a learning experience. Even I learn from these questions at times.
One method I used for a similar problem was to put a shortlived trigger on the table to log the host and user names of anyone writing to that table. You could perhaps also call [dbcc inputbuffer][1] inside the same trigger, and extract the EventInfo to get the sql commands or procedure name being used. I'm assuming the overhead of introducing a trigger is neglible if you're looking at using profiler, and the table is old and mysterious. [1]:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-inputbuffer-transact-sql
19 People are following this question.
