如何检索Select查询的列标题?

How it is possible to retrieve column headers of a select query as a single column in SQL Server ? (it is preferred to retrieve data type of columns )

如何将select查询的列头作为SQL Server中的单个列检索?(最好检索列的数据类型)

Query example:

查询的例子:

select a.PartId, a.PartName, b.GroupName 
from Parts as a 
inner join Groups as b on a.GroupRef = b.GroupId

Expected result:

预期结果:

Columns 
--------
PartId
PartName
GroupName

3 个解决方案

#1

One way is to create a temporary table with the schema of resultset and then query tempdb's schema table to get the column names and details. You can get all needed details.

一种方法是使用resultset的模式创建一个临时表，然后查询tempdb的模式表以获取列名和详细信息。你可以得到所有需要的细节。

    select a.PartId , a.PartName , b.GroupName into #yourtable
    from Parts as a inner join Groups as b 
    on a.GroupRef = b.GroupId
    where 1=2

    SELECT c.name as columnname,t.name as datatype
    FROM tempdb.sys.columns c
            inner join tempdb.sys.systypes as t on t.xtype = c.system_type_id
    WHERE [object_id] = OBJECT_ID(N'tempdb..#yourtable');

#2

Starting from SQL Server 2012+ you can use sys.dm_exec_describe_first_result_set to get all metadata about result set:

从SQL Server 2012+开始，你可以使用sys。dm_exec_descripbe_first_result_set获取关于结果集的所有元数据:

DBFiddle Demo

DBFiddle演示

DECLARE @tsql NVARCHAR(MAX) = 
  N'select a.PartId , a.PartName , b.GroupName 
from Parts as a inner join Groups as b 
on a.GroupRef = b.GroupId';

SELECT name AS [Columns]
FROM sys.dm_exec_describe_first_result_set(@tsql, NULL, 1)

#3

    SELECT 'PartId', 'PartName', 'GroupName'
    UNION ALL
    select a.PartId , a.PartName , b.GroupName 
    from Parts as a inner join Groups as b 
    on a.GroupRef = b.GroupId

#1