如何从 SQL Server 中的查询中获取列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31297980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get column names from a query in SQL Server
提问by WebDevGuy2
Using SQL Server.
使用 SQL Server。
I have a very extensive query, with a lot of aliasing, etc...
我有一个非常广泛的查询，有很多别名等......
Is there a way, using just SQL (stored proc is fine, but not PHP, etc), to get a list of all column names from this query? (I realize I will have to probably embed my query inside of this solution but that is fine. Just a temporary measure.)
有没有办法，只使用 SQL（存储过程很好，但不是 PHP 等），从这个查询中获取所有列名的列表？（我意识到我可能不得不将我的查询嵌入到这个解决方案中，但这很好。只是一个临时措施。）
Thanks!
谢谢！
回答by Conrad Frix
If you're using SQL Server 2012 or later you can take advantage of sys.dm_exec_describe_first_result_set
如果您使用的是 SQL Server 2012 或更高版本，则可以利用sys.dm_exec_describe_first_result_set
SELECT name
FROM
sys.dm_exec_describe_first_result_set
('Your Query Here', NULL, 0) ;
回答by Gordon Linoff
This is too long for a comment.
评论太长了。
There are various ways that you can get the columnsout of the query, such as:
您可以通过多种方式从查询中获取列，例如：
select top 0 s.*
from (<your query here>) s;
Then you can parse the results.
然后就可以解析结果了。
However, I have found another approach useful. Create either a view or a table using the same logic:
但是，我发现另一种方法很有用。使用相同的逻辑创建视图或表：
select top 0 s.*
into _TempTableForColumns
from (<your query here>) s;
Then use information_schema(or the system tables if you prefer):
然后使用information_schema（或系统表，如果您愿意）：
select *
from information_schema.columns
where table_name = '_TempTableForColumns' and schema_name = 'dbo';
drop table _TempTableForColumns;
The advantage of this approach is that you can get type information along with the column names. But the engine still has to run the query and that might take time even though no rows are returned. Although the column names and types are available after compiling, I am not aware of a way to get them without also executing the query.
这种方法的优点是您可以获得类型信息以及列名。但是引擎仍然必须运行查询，即使没有返回任何行，这也可能需要一些时间。尽管列名和类型在编译后可用，但我不知道有什么方法可以在不执行查询的情况下获取它们。
回答by Jason Lou
After SQL Server 2008
SQL Server 2008 之后
select *
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where o.name = 'TableName'
Before
前
select *
from syscolumns c
inner join sysobjects o on c.id = o.id
where o.name = 'TableName'
