获取SQL查询中使用的字段

Question

我希望能够返回给定SQL查询所使用的所有字段的列表(最好带有表的详细信息(。例如，查询的输入：

SELECT t1.field1, field3
FROM dbo.table1 AS t1
INNER JOIN dbo.table2 as t2
ON t2.field2 = t1.field2
WHERE t2.field1 = 'someValue'

将返回

+--------+-----------+--------+
| schema | tablename | field  |
+--------+-----------+--------+
| dbo    | table1    | field1 |
| dbo    | table1    | field2 |
| dbo    | table1    | field3 |
| dbo    | table2    | field1 |
| dbo    | table2    | field2 |
+--------+-----------+--------+

实际上，它需要使用SQL内核(这个词对吗？engine？(，因为读者无法知道字段3在表1而不是表2中。出于这个原因，我认为解决方案应该是SQL。如果它也能处理SELECT *，则可获得额外积分！

我尝试了使用sqlparse的python解决方案(https://sqlparse.readthedocs.io/en/latest/)，但在处理涉及临时表、子查询和CTE的更复杂的SQL查询时遇到了问题。此外，别名的处理也非常困难(尤其是当查询在多个地方使用相同的别名时(。显然，它无法处理像上面的field3这样没有表标识符的情况。它也不能处理SELECT *。

我希望SQL Server Management Studio中可能有一个更巧妙的解决方案，甚至SQL Server本身也有一些功能。我们有来自Redgate的SQL Prompt，它必须在其智能意义上了解它正在格式化的体系结构和SQL查询。

更新：根据要求：我之所以尝试这样做，是为了找出哪个Users可以在我们的组织内执行哪些SSRS报告。这完全取决于他们在给定报告中的所有数据集(在我们的例子中是SQL查询(使用的所有字段上是否为其Roles分配了GRANT SELECT权限。我已经根据Roles报告了哪些Users在哪些字段上具有GRANT SELECT。我现在想扩展那些权限允许它们运行的报表。

Answer 1

列表名称可能很棘手，因为列名可能不明确，甚至是派生的。但是，您可以从几乎任何查询或存储过程中获取列名、序列和类型。

示例

Select column_ordinal
,name
,system_type_name 
From sys.dm_exec_describe_first_result_set('Select * from YourTable',null,null )  

Answer 2

我想我现在已经找到了答案。请注意：我目前没有执行这些功能的权限，所以我还没有测试它-我会在有机会测试它时更新答案。谢谢你的回答。答案如下：https://stackoverflow.com/a/19852614/6709902

解决了选择SQL Server执行计划中使用的所有列的最终目标：

USE AdventureWorksDW2012
DBCC FREEPROCCACHE
SELECT dC.Gender, dc.HouseOwnerFlag, 
SUM(fIS.SalesAmount) AS SalesAmount 
FROM 
dbo.DimCustomer dC INNER JOIN
dbo.FactInternetSales fIS ON fIS.CustomerKey = dC.CustomerKey 
GROUP BY dC.Gender, dc.HouseOwnerFlag
ORDER BY dC.Gender, dc.HouseOwnerFlag
/*
query_hash          query_plan_hash
0x752B3F80E2DB426A  0xA15453A5C2D43765
*/
DECLARE @MyQ AS XML;
-- SELECT qstats.query_hash, query_plan_hash, qplan.query_plan AS [Query Plan],qtext.text 
SELECT @MyQ = qplan.query_plan 
FROM sys.dm_exec_query_stats AS qstats 
CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan 
cross apply sys.dm_exec_sql_text(qstats.plan_handle) as qtext 
where text like '% fIS %'
and query_plan_hash = 0xA15453A5C2D43765
SeLeCt @MyQ
;WITH xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
[Database] = x.value('(@Database)[1]', 'varchar(128)'),
[Schema]   = x.value('(@Schema)[1]',   'varchar(128)'),
[Table]    = x.value('(@Table)[1]',    'varchar(128)'),
[Alias]    = x.value('(@Alias)[1]',    'varchar(128)'),
[Column]   = x.value('(@Column)[1]',   'varchar(128)')
FROM   @MyQ.nodes('//ColumnReference') x1(x)

导致以下输出：

Database                  Schema Table            Alias Column
------------------------- ------ ---------------- ----- ----------------
NULL                      NULL   NULL             NULL  Expr1004
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  CustomerKey
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  Gender
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  HouseOwnerFlag
[AdventureWorksDW2012]    [dbo]  [FactInternetSal [fIS] CustomerKey
[AdventureWorksDW2012]    [dbo]  [FactInternetSal [fIS] SalesAmount