Get Column Names of a Query in SQL Server

Question

Let's say I have a query in SQL 2014:

SELECT EmployeeName, EmployeeAddress, EmployeeAge FROM dbo.Employee

I would like to dynamically go through the query, loop and get the name of the columns like EmployeeName, EmployeeAddress, and EmployeeAge.

I need this because I can have another query different than this I need to get the column names as well.

Answer 1

The sp_describe_first_result_set stored procedure will give you the column names and much more for any query. You simply need to pass the query in question to the @tsql parameter. Please see below example use of the stored procedure:

DECLARE @queryDescription TABLE
(
     s_hidden                       bit             NULL
    ,column_ordinal                 int             NULL
    ,name                           sysname         NULL
    ,is_nullable                    bit             NULL
    ,system_type_id                 int             NULL
    ,system_type_name               nvarchar(256)   NULL
    ,max_length                     smallint        NULL
    ,precision                      tinyint         NULL
    ,scale                          tinyint         NULL
    ,collation_name                 sysname         NULL
    ,user_type_id                   int             NULL
    ,user_type_database             sysname         NULL
    ,user_type_schema               sysname         NULL
    ,user_type_name                 sysname         NULL
    ,assembly_qualified_type_name   nvarchar(4000)  NULL
    ,xml_collection_id              int             NULL
    ,xml_collection_database        sysname         NULL
    ,xml_collection_schema          sysname         NULL
    ,xml_collection_name            sysname         NULL
    ,is_xml_document                bit             NULL
    ,is_case_sensitive              bit             NULL
    ,is_fixed_length_clr_type       bit             NULL
    ,source_server                  sysname         NULL
    ,source_database                sysname         NULL
    ,source_schema                  sysname         NULL
    ,source_table                   sysname         NULL
    ,source_column                  sysname         NULL
    ,is_identity_column             bit             NULL
    ,is_part_of_unique_key          bit             NULL
    ,is_updateable                  bit             NULL
    ,is_computed_column             bit             NULL
    ,is_sparse_column_set           bit             NULL
    ,ordinal_in_order_by_list       smallint        NULL
    ,order_by_list_length           smallint        NULL
    ,order_by_is_descending         smallint        NULL
    ,tds_type_id                    int             NULL
    ,tds_length                     int             NULL
    ,tds_collation_id               int             NULL
    ,tds_collation_sort_id          tinyint         NULL

)


DECLARE @query NVARCHAR(MAX) = 'SELECT EmployeeName, EmployeeAddress, EmployeeAge FROM dbo.Employee'

INSERT INTO @queryDescription
EXEC sp_describe_first_result_set @tsql = @query


SELECT  Name AS ColumnName
        ,system_type_name AS DataTypeName
        ,column_ordinal AS Ordinal
FROM    @queryDescription

Answer 2

This will return a comma-delimited list of the columns for the table named therein.

SELECT      
(
SELECT DISTINCT  STUFF( ( SELECT  ',' + isc.name + ''
FROM sys.columns isc
WHERE OBJECT_NAME(isc.object_id) = 'TableName' FOR XML PATH('') ), 1,1,'')
AS SqlScript
)