October 6, 2009 at 8:11 am
Hello All! I am a SQL 2005 Newbie and I have a question that hopefully, someone will be able to assist me with.
I have a column in a table and I need to find out how/where that column gets updated within the database.
I have searched all of the source code of the application that uses the database and I do not see any instance where the column in question gets inserted/updated. That leads me to believe that the insert/update must be occurring somewhere in the DB.
So, I am looking for information on how to "find" such. I am sure there must be some type of script, function, view or tool that would allow me to search the contents of the database for this info. Can someone point me in the right direction on this ?
I am using Microsoft SQL Server Management Studio version 9.00.3042.00
Thanks!
Everett
October 6, 2009 at 8:25 am
You may want to start off with running a Profiler session on the specific database. Open SQL Server Profiler, Create a new trace (File --> New Trace). Log in to the server on which the database in question resides. You will need to have sa access to the SQL instance you wish to monitor.
Leave the first tab at its defaults (the Standard template), and in the Events Selection tab, make sure RPC:Completed and SQL:BatchCompleted are checked. In the lower right-hand quadrant, check the "Show All Columns" check box, and scroll right to make sure the DatabaseName column is checked. You'll also want to ensure that other columns like LoginName, TextData, HostName, and ApplicationName are selected for all events you're capturing.
Finally, click the "Column Filters" button, click on "DatabaseName" in the List Box, and expand the "Like" container. Include the database name you want to monitor, then click OK, then Run. I always log in through SSMS and run a simple query against that specific database to make sure my query comes through in the trace and that I have the columns I want to look at. If everything looks OK, let the trace run and periodically look at the commands run against the database to see if code exists that mentions the target table or column name.
If you aren't able to find anything there, you may want to look at installing a trigger (temporarily) that will log SUSER_SNAME(), GETDATE(), and other environment variables to an external table when that specific column is updated. Only thing there is that requires a code change which you may not be able to just implement at the drop of a hat. Let us know if you want more details on how to go about doing that.
Hope this helps:
MJM
October 6, 2009 at 8:30 am
Mark:
Thank you very much for your reply! That is excellent information and I will commence to getting that trace set up and logging asap.
Again, Many Thanks!
Everett
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply