June 16, 2009 at 9:13 pm
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
June 17, 2009 at 7:45 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 17, 2009 at 8:44 pm
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
June 18, 2009 at 5:47 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 18, 2009 at 3:27 pm
[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]
June 18, 2009 at 11:12 pm
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.:-)
June 19, 2009 at 12:33 am
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
June 19, 2009 at 3:33 am
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.
June 19, 2009 at 4:14 am
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
June 19, 2009 at 5:49 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply