July 20, 2006 at 12:53 pm
I am fairly new at this and I need to find out how a table is being populated. I have gone through all the dts packages and cannot find any information on it. I also checked the dependencies on that table which gave me no answers.
Is there a way to find out what exactly is updating the table through query analyzer or if there are other ways of finding this out.
July 20, 2006 at 1:04 pm
Not sure exactly what you are after but Profiler may be the tool you are looking for
* Noel
July 20, 2006 at 2:21 pm
Thanks. There is a table that gets populated somehow and the previous DBA does not have this documented therefore I need to know how the data is getting populated into that table.
July 20, 2006 at 2:33 pm
Profiler should be your first line if investigation, then.
You could also potentially put a trigger on the table to capture data, but I'd start with Profiler.
July 20, 2006 at 2:51 pm
I am using profiler and found the objectid for that table. Do you recommend any other parameters to capture this information
July 20, 2006 at 3:17 pm
What kind of information are you looking for?
Coincidentally, Steve Jones posted an article today that discusses using Profiler (in a different context but it should help you out). You can get there from the home page.
July 20, 2006 at 7:46 pm
You could always try to set up a table to audit the actual data being inserted.
Essentially, create a separate table withte the following columns
CREATE TABLE audit_table_name
(
date DATETIME NULL DEFAULT (GETDATE())
, system_user_name VARCHAR(50) NULL DEFAULT SUSER_SNAME()
, user_name VARCHAR(50) NULL DEFAULT USER_NAME()
, host_name VARCHAR(50) NULL DEFAULT HOST_NAME()
, app_name VARCHAR(50) NULL DEFAULT APP_NAME()
, nest_level INT NULL DEFAULT @@NESTLEVEL
, proc_id INT NULL DEFAULT @@PROCID
... then add the tables columns if you want to see what has changed.
)
Create a trigger on the table in question to insert when and insert, update or delete happens.
The defaults will record all the information you need.
WARNING - this is invasive to the production system in that you are introducing a whole new level of inserts into your normal transactions. Be careful of possible affects on the performance of the system.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply