I am trying to get the list of all tables used in a SQL script, using TSQL
For Example
SQL Script:
SELECT t1.Name, t2.Age
FROM TableOne t1 LEFT JOIN
TableTwo t2
ON T1.Number=T2.Number
expected Output:
TableOne
TableTwo
Thanks in advance
This is just idea and not fully-baked solution.
You could use execution plan to get object names:
-- in the same session
SELECT t1.Name, t2.Age
FROM TableOne t1 LEFT JOIN
TableTwo t2
ON T1.Number=T2.Number
;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
AS ns)
,cte AS (
SELECT t.text, p.query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE r.session_id = @@SPID
), val AS (
SELECT DISTINCT s.c.value('@Schema', 'SYSNAME') AS [schema],
s.c.value('@Table', 'SYSNAME') AS [table_name]
FROM cte c
CROSS APPLY c.query_plan.nodes('//ns:Object') AS s(c)
)
SELECT *
FROM val
WHERE [schema] IS NOT NULL;
Be aware that it could return dependent objects(for example when view/table function is used).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments