CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Behnam
Behnam

Reputation: 1063

How to Retrieve Column Headers of a Select Query?

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

Upvotes: 4

Views: 12839

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

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)

Upvotes: 6

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

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.

    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');

Upvotes: 2

ProblemSolver
ProblemSolver

Reputation: 644

    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

Upvotes: 1

Related Questions