CODEWITHSUNDEEP
sqlsql-server
Tom Sharpe
Tom Sharpe

Reputation: 34180

Find which tables are used by a set of queries

I have a database with many tables and a set of queries which are going to be used for a particular application. The queries only use a small subset of the tables in the database and it has been decided to create a new database with just the set of tables which are referenced by the set of queries.

Is there a way to list the tables which are used by a set of queries?

The table names in this case are usually enclosed in square brackets so I can get so far by searching on this e.g. using 'Find In Files' in SQL Server management Studio. Is there a better way, perhaps using the fact that table names are usually preceded by the word 'FROM' or 'JOIN' ?

Upvotes: 2

Views: 91

Answers (1)

Michael G
Michael G

Reputation: 6745

If these queries are stored procedures, you can right click on them in SSMS and select View Dependencies.

View Dependencies

Once selected, the Object Dependencies dialog will show, and you will want to toggle the radio button to "Objects on which [Stored procedure Name] depends.

Another option is to generate an estimated query plan, and parse the XML that it generates.

Upvotes: 4

Related Questions