Analyzing SQL Queries

Question

I have a set of MS sql queries (around 500). I have to analyze these queries and find out the most frequently used tables, filter conditions . Is there an easy way to do this. Can we use R to do this? Or some other tool or logic.

Answer 1

There is no easy way but ...................

Text You can get all your views, functions, stored procedures etc scripted out of the database in SSMS by right clicking on the database then Tasks -> Generate Scripts and then following the wizard. When you have them out in a text file you may be able to process them in bulk using other tools.

As Variables that can be processed with SQL Again there is no way to automatically do what your want but you can get the text of your sp's etc into tables. To get the text of an stored procedure etc into a set of table rows you can use exec sp_helptext and then process those records/lines against table names in your database (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE')