SQL Profiler: writing records for a specific table (s)

Question

I need to write write operations (any query that modifies data), but only for two specific tables. Is it possible to configure this in Sql Profiler? If so, how? I hope for a solution that does not include text filtering.

Answer 1

Based on comments on other answers use SQL Profiler as you want with the following setting

Event Selection: -Stored Procedures - SP: StmtStarting -TSQL - SQL:StmtStarting Column Filters -TextData (LIKE) - "insert %" - "update %" - "delete %" 

I did not want to filter text data, but this is the only way to get what I want.

Answer 2

Use a change data record, which is explained in detail here.

If CDC is not your choice, consider placing the trigger in the table (s). Functions such as

 select App_name() as 'AppName', CURRENT_USER as 'CurrentUser', HOST_ID () as 'HostID', HOST_NAME () as 'HostName', SESSION_USER as 'SessionUser', SYSTEM_USER as 'SystemUser' 

Then put the data in the history table

Answer 3

I agree with another answer that CDC is the way to go (while you work in the Enterprise edition)

Using a profiler is technically possible, but only a little.

You will start by capturing TSQL: StmtStarting, TSQL: StmtCompleting

Then you will need to export the list for further analysis. You will need to write some kind of analyzer to filter out statements that did not meet the criteria. If your system receives ad-hoc requests or is plastered by a system that generates a large number of requests, analyzing this list will quickly become exotetially hairy.

There is also the problem that starting the profiler has significant overhead, and if you wanted to get these numbers for a long period of time, you would quickly put system resources at unacceptable levels for most production systems.

In short, it might seem fun to try using a profiler in this way, but not in a "real world" scenario.

And this comes from a guy who likes to watch racing on lawn mowers.