How to Retrieve Column Headers of a Select Query?

Question

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 )

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

Answer 1

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

DBFiddle Demo

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)