Way to find out Table names used in 'inline queries'

  • Hi,

    I have a application with more than 3000 pages. i have to take a list of Tables used in the application. i can take the table names used in SPs. but they have used more inline queries in the application. this i should get from the profiler only.

    is there any other easy way to find out the tables used in 'inline' queries?

    Thanks,

    Regards,

    Viji

  • I think you've already hit the best option, use trace events to see what calls have come through. You can also query the system cache to see what's there, but understand that if queries have aged out of cache, they won't be there. Use sys.dm_exec_sql_text.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Grant Fritchey,

    Thanks for your reply. my problem is the profiler fetches many rows and filtering table names alone from it is a painful job. Instead I'm using the following query to find out the tables used in the SPs.

    SELECT DISTINCT

    o.name , oo.name

    FROM sysdepends d, sysobjects o, sysobjects oo

    WHERE o.id=d.id

    and o.name= @ProcName -- Stored Procedure Name

    and oo.id=d.depid and depnumber=1

    ORDER BY o.name,oo.name

    This gives all the Table Names used in the SPs.

    Same way is there any way to get only the inline queries from the profiler? so that i can filter the table names from it?

    Thanks,

    Regards,

    Viji

  • First, don't run the profiler gui on your production systems. Instead use profiler to create a server-side trace (do a search on that phrase, it'll tell you everything you need to know). Set the trace up to output to a file. Take the file and load it into a table (don't trace directly to a table). Once the trace data is in the table, you can query it to your heart's content to get exactly what you need.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • [font="Verdana"]Might be worth having a look at Gail Shaw's excellent article on performance issues. She goes through how to use trace on the server.

    Also, you can use the object_name(id) function.

    [/font]

  • SELECT DISTINCT

    o.name , oo.name

    FROM sysdepends d, sysobjects o, sysobjects oo

    WHERE o.id=d.id

    and o.name= @ProcName -- Stored Procedure Name

    and oo.id=d.depid and depnumber=1

    ORDER BY o.name,oo.name

    THIS WILL NOT GIVE YOU THE CORRECT RESULT.:-)

  • SELECT DISTINCT

    o.name , oo.name

    FROM sysdepends d, sysobjects o, sysobjects oo

    WHERE o.id=d.id

    and o.name= @ProcName -- Stored Procedure Name

    and oo.id=d.depid and depnumber=1

    ORDER BY o.name,oo.name

    THIS WILL NOT GIVE YOU THE CORRECT RESULT.

    May i know why?

    regards

    viji

  • sysdepends does not contains all dependent table informations. best approch is keep trace on and capture the data in a text file import that text file to a table the run query to find out the dependent table.

    I don't know proper reson why sysdepends does not contain all dependent table information but some long time ago, I have faced problem.

  • Hi Abhijit,

    The trace gives me very lengthy reports if I query those to get eventclass in (12,13) would that be sufficient?

    Again I have to get table names from Inline queries also. Is there any strategy to do like

    SELECT textdata FROM TPflrResult WHERE textdata LIKE '%TABLENAME%' AND eventclass IN (12,13)

    The above query to work fine I should include all the table names using cursor.

    I'm searching for better option.

    Thanks,

    Regards,

    Viji

  • Have you tried querying the dynamic management view sys.dm_exec_query_text yet? Any inline queries will be visible there, for the time that they're in cache.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 10 posts - 1 through 9 (of 9 total)

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