asked on

SQL - How to list all tables participating in a query

Dear Experts,

I have a very complex .sql file to be analyzed in SQL on Microsoft SQL Server Management Studio.

Basically it would be important to know regarding that query, that which tables participants in that as source and when they were updated?

Could you please advise is there some way in SQL to check from which tables a query is feeded?

Thanks in advance,

Pawan Kumar

Can you please provide the sql file , how complex it is?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Thanks for the advices, I can just provide some overall statistics about the code, it is around 500 lines long and having 60 joins.

If there is no such tool then I will start so the manual analysis

csehz

ASKER

Máté Farkas

It is possible to extract all tables (and columns from a query) with the following steps:
1. Make sure that your query contains only 1 SELECT/UPDATE/INSERT/DELETE/MERGE statement
2. Turn on "Include Actual Execution Plan option in SSMS"
3. Execute the query and after the execution finishes switch to "Execution Plan" tab on results pane
4. Right click on execution plan stage and select "Show Execution Plan XML..." option
5. Clear namespace and other definitions in ShowPlanXML node (just <ShowPlanXML> must remain)
6. Select all the displayed execution plan XML and copy to clipboard
7. Paste XML code into this script

DECLARE @XML AS XML = '
... paste showplan XML here and then replace ' to ''
';
SELECT DISTINCT result.[Database], result.[Schema], result.[Table]
FROM (
        SELECT  v.value('@Database', 'varchar(100)') AS [Database],
                v.value('@Schema', 'varchar(100)') AS [Schema],
                v.value('@Table', 'varchar(100)') AS [Table],
                v.value('@Column', 'varchar(100)') AS [Column],
                v.value('@Alias', 'varchar(100)') AS [TableAlias]
        FROM @XML.nodes('//ColumnReference') n(v)
) result
WHERE result.[Table] IS NOT NULL;

Open in new window

8. Replace ' (apostrophe) to '' (2 apostrophes) but NOT to double quote (") in xml (and not in whole SQL script)
9. Execute the script. It lists all used tables.