x

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!

more ▼

asked Apr 25 at 01:36 PM in Default

avatar image

sukichweet
1

@sukichweet ,

Any feedback on any of the suggestions below? This IS a two way street! ;)

May 05 at 11:42 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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/

more ▼

answered Apr 25 at 06:43 PM

avatar image

sjimmo
1.6k 1 3 5

What if the OP is using an ORM such as Entity Framework to populate the table?

Apr 25 at 08:29 PM JohnM

They should at the very least be able to see where the sp is being called from. That should help point them in a direction to look.

Apr 25 at 09:15 PM sjimmo

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.

Apr 30 at 02:46 PM Jeff Moden

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.

May 01 at 03:39 PM sjimmo
(comments are locked)
10|1200 characters needed characters left

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 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.

more ▼

answered May 17 at 08:16 AM

avatar image

knightwisp
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x410

asked: Apr 25 at 01:36 PM

Seen: 92 times

Last Updated: May 17 at 08:16 AM

Copyright 2016 Redgate Software. Privacy Policy