Using SQL Profiler to identify tables used

  • I want to run a trace that will capture all inserts statements, the end result being a list of tables that are touched by an insert process by the application.

    I've store the trace results in a table having used SQLProfilerTSQL_Sps trace.

    Using a like statement on the resultant trace table I get 1000's of rows and would mean I have to trawl through each one manually finding the table names

    Is there a better way of doing this !? please say there is !!!!

  • In profiler there is no stats option. But if you can alter the table you could add a tigger to create stats into another table. I also believe (which I forget the names) there are a few apps out there that can do what you are asking.

  • I'm not after any stats , all I want to know is which tables have data inserted into them during a certain process i.e the end result being a list of table names

    thanks Si

  • This might get you closer.

    select

    substring(textdata,

    charindex('insert into',textdata), -- find the INSERT INTO

    100) -- 100 characters should be more than enough to get the insert into plus the table name

    as Inserts

    from

    Your_Trace_Table

    where

    textdata like '%insert into%'

    From here it might be easiest to cut and paste into a text editor or spreadsheet to do some final string manipulation or regex matching.

  • Save the trace as a SQL script and look for the INSERT statements in the resuling .sql file.

  • Grasshopper-that is smart!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply