SQL查询中使用的表的列表 [英] List of tables used in an SQL Query

问题描述

有没有一种方法来获取在SQL查询中使用的表的列表?例子 :我有类似的东西:

Is there a way how to get a list of tables used within an SQL query? Example : I have something like :

SELECT * FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id

我希望得到

Table, OtherTable

谢谢

推荐答案

您可以在查询后立即使用此sql脚本.它将返回上一次执行的查询中使用的表的列表:

you can use this sql script right after your query. It will return a list of tables used in the last executed query:

   SELECT Field1, Field2 
   FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id

  ;WITH vwQueryStats AS(
     SELECT 
      COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName
      ,execution_count
      ,s2.objectid
      ,(
         SELECT TOP 1 
            SUBSTRING(s2.TEXT,statement_start_offset / 2+1 
            ,( ( CASE WHEN statement_end_offset = -1
                THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
                ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement
            ,last_execution_time
         FROM sys.dm_exec_query_stats AS s1
         CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
    )
    SELECT TOP 1 * 
    INTO #lastQueryStats
    FROM vwQueryStats x
    WHERE sql_statement NOT like 'WITH vwQueryStats AS%'
    ORDER BY last_execution_time DESC

    SELECT
    TABLE_NAME
    FROM #lastQueryStats, INFORMATION_SCHEMA.TABLES tab 
    WHERE CHARINDEX( tab.TABLE_NAME, sql_statement) > 0


    DROP TABLE #lastQueryStats 

我采用了从此帖子中检索最后执行的查询的查询我对它进行了一些修改，使其与您的示例匹配.

I've taken the query that retrieves the last executed query from this post and I modified it a bit to match with your example.

输出将按照您的要求:

 Table
 OtherTable

然后，如果要将逗号分隔，可以执行以下操作:

Then if you want to have them comma separated you can do:

DECLARE @tableNames VARCHAR(MAX) 

SELECT @tableNames = COALESCE(@tableNames + ', ', '') + TABLE_NAME
FROM   #lastQueryStats, INFORMATION_SCHEMA.TABLES tab 
WHERE  CHARINDEX( tab.TABLE_NAME, sql_statement) > 0

SELECT @tableNames 

但是，您应该警惕，在同时执行数千个查询的正常"生产或QA环境中，这可能不起作用，因为可以在您的第一个查询和从数据库统计信息中提取信息的查询之间执行另一个查询.

However you should be wary that in a 'usual' production or QA environment with thousands of query executed concurrently this mightn't work as another query could be executed in between your first query and the query that extracts info from db stats.

希望有帮助

这篇关于SQL查询中使用的表的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！