drew.allen (8/28/2015)
I actually prefer using sys.dm_sql_referencing_entities. I found that I was getting false positives using the LIKE operator, because people had commented out sections of code, so the table name was still in the definition, but as a comment.Drew
This will only work for T-SQL coded objects (views, stored procedures, etc.)
If you have access to a SQL Server instance where you can ensure that nothing is running on it, then what I'm thinking is to run DBCC FREEPROCCACHE to drop all queries from the plan cache. Then run all of the scripts, and finally query the plan cache to get the tables. A little bit of XQuery will get the tables involved in the queries. However, this will also get queries that SQL runs behind the scenes. You might be able to filter out by a database.
Do you need to have this list of tables per script? Or just an overall all encompassing list of unique tables used by all of the scripts.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes