Post reply

The best way to check if and how specific tables are being used

  • June 7, 2010 at 10:16 pm

    #145169

    Hi,

    I need to check if three of the tables in one of the database are being used by anything but the nightly batch process (e.g. reporting).

    I've set up a standard trace with the following events:

    Stored Procedures

    RPC:Completed

    TSQL

    SQL:BatchCompleted

    SQL:BatchStarting

    I've put in a filter to filter only the events where the database name is like '%MyDBName%'.

    Is there a better way of doing this?

    Thanks.

  • June 8, 2010 at 8:33 am

    #1177505

    You could search your SPs for the table name. It won't tell you if the SPs are used, but can be helpful in general.

    select name, crdate, CHARINDEX('TableName', definition),substring(definition,CHARINDEX('TableName', definition)-25,150) as 'Code Snippet', definition as 'Entire Code'

    from sys.sql_modules com

    join sysobjects obj on com.object_id = obj.id

    where definition like '%TableName%'

    order by name

  • June 8, 2010 at 8:19 pm

    #1177767

    Already done this. I was just wondering if I could do any better with the trace.

Viewing 3 posts - 1 through 3 (of 3 total)

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