3

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.

sandeep johnson
  • 161
  • 2
  • 7
  • This is a fairly big and ugly job, especially if the queries could be complex, nested, etc. Start by writing a parser to handle one query. Then try it on other queries and see if it works there too. – Tim Biegeleisen Jun 26 '18 at 06:24
  • 1
    I think you've to treat the queries as text (structured, maybe simpler), look at [tm package](https://cran.r-project.org/web/packages/tm/tm.pdf) for text mining. – s__ Jun 26 '18 at 06:26
  • I think you can also use [pandas library](https://www.datacamp.com/community/tutorials/pandas-tutorial-dataframe-python) in Python. Check it out if it helps. – Mayank Porwal Jun 26 '18 at 07:17
  • The reality here is that if you are also looking into performance the best is to run them in SQL Server and to include Execution plan. There are a lot more information that you would need beside just the table like also if there is index scanning or other performance bottlenecks. From just table perceptive, I would build a dictionary of tables (sys.tables) and check between the FROM clause and the next clause for each of the tables (string search). You would need to cross your fingers that your naming convention is not mixing tables and field names. – Arnaud Jun 26 '18 at 13:12
  • Do you care more about the number of times an object is referred, or the actual number of reads/writes of that object over a certain period? – The Lyrist Jul 09 '18 at 15:39

2 Answers2

2

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')

Gerard
  • 301
  • 2
  • 9
1

There is a package in python called sqlparse which helps to parse and seperate tokens in sql queries. But still a long way to go to achieve what I need. Any suggestions are welcome.

sandeep johnson
  • 161
  • 2
  • 7