Go to Top

Find all queries that use a particular table

We just completed a massive 39 database conversion to SQL Server for a client in Tennessee which was quite challenging, we had to not only upsize all the tables, but switch the system from importing/exporting between databases to using linked tables instead. At times we needed to find which queries was using a table, so we used the following code to quickly find a table in all of the queries:

Let’s say we need to find where tblCustomers is being used in the query collection, we would press Ctrl-G to go to the Immediate window and type:

The system will return all the queries where used in the same immediate window:

I hope you find this code helpful! Let me know in the comments, anyone leaving a comment will be entered into a $25 Amazon.com gift certificate, hurry, contest ends 6/10/16!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

5 Responses to "Find all queries that use a particular table"

  • marc
    June 10, 2016 - 2:28 am Reply

    Very good, thanks. A good alternative to using the in-built linkage tool.

  • Roland
    June 9, 2016 - 8:27 pm Reply

    Very smart and useful function! I like it.
    Thank you for sharing it!

  • Mark
    June 9, 2016 - 5:24 pm Reply

    Thank you so much. This is a huge help for debugging systems. When I tried to use the code I did get an error on the line… If InStr(1……). It seemed to have a problem with the >. I’m not familiar enough with the syntax to know why. Should this result in an error or am I doing something incorrect?
    Thanks

  • Oleg
    June 9, 2016 - 2:35 pm Reply

    A good and useful example !!!

  • Jeff Coulson
    June 9, 2016 - 2:27 pm Reply

    The simplest little clusters of code can result in such powerful information making huge tasks so easy. Thanks Juan!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us