How to get the table names from "SQL script" in SQL Server database

Question

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

Put it into a stored proc then use sys.dm_sql_referenced_entities on the proc. — Martin Smith
– Martin Smith, Commented Jul 29, 2017 at 18:35
If it is just a single query, you can also use a view or use sys.sql_expression_dependencies. — Gordon Linoff
– Gordon Linoff, Commented Jul 29, 2017 at 18:36

Lukasz Szozda · Accepted Answer · 2017-07-29 19:08:08Z

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).

Idleguys · Accepted Answer · 2017-07-29 19:53:06Z

All,

Thank you for your recommendations.

Unfortunately I cannot use execution plans as these scripts are saved in a table. Also I cannot create stored procedures on top of these scripts as we have 300+ scripts. However I have started with below scripts where it is helping me to get the tables so far. but there are few scenario (like Sub query) where I am still working. Thanks again

WHILE (@i<=25)
BEGIN
SELECT @SQLScript=SQLQuery 
		,@TargetTableName=TargetTableName
		,@TargetDB=TargetDB 
		FROM #T WHERE Number=@i

  SELECT @MaxLen=LEN(@SQLScript) 

	IF EXISTS (SELECT  1 FROM #T WHERE  @SQLScript LIKE '%JOIN%')
	BEGIN
  
		SELECT @TargetTableName AS TargetTableName
				,SUBSTRING(@SQLScript,CHARINDEX('FROM',@SQLScript)+5 ,CHARINDEX(' ',SUBSTRING(@SQLScript,CHARINDEX('FROM',@SQLScript)+5,@MaxLen))) AS TableUsed
		UNION

		SELECT @TargetTableName AS TargetTableName
				,SUBSTRING(@SQLScript,CHARINDEX('JOIN ',@SQLScript)+5,CHARINDEX(' ',SUBSTRING(@SQLScript,CHARINDEX('JOIN ',@SQLScript)+5,@MaxLen))

		)
		END
	IF EXISTS (SELECT 1 FROM #T WHERE CHARINDEX(' ',SUBSTRING(@SQLScript,CHARINDEX('FROM',@SQLScript)+5,@MaxLen))=0)-- Applies where there is SQLquery ends with single table
	BEGIN


	SELECT   @TargetTableName AS TargetTableName,SUBSTRING(@SQLScript,CHARINDEX('FROM',@SQLScript)+5,@MaxLen)
		--EXECUTE sp_executesql @SQLScript
		
	END
	SET @i=@i+1
END

Collectives™ on Stack Overflow

How to get the table names from "SQL script" in SQL Server database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related