CODEWITHSUNDEEP
sql-serversql-server-2014
carlosm
carlosm

Reputation: 755

Get Column Names of a Query in SQL Server

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.

Upvotes: 4

Views: 7487

Answers (4)

Edmond Quinton
Edmond Quinton

Reputation: 1739

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

Upvotes: 5

LogicalMan
LogicalMan

Reputation: 384

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
)

Upvotes: 1

SlimsGhost
SlimsGhost

Reputation: 2909

Well, since the query can have complicated expressions that get resolved to "column" names, and they can have arbitrary names (as long as they're legal identifiers), you are looking at a string-parsing party!

Generally, a column name/alias will be either at the end of the column expression (before the comma or the start of the FROM clause), or some dbms allow you to also do "select myname=a+b, anothername=c+d, ...", and you can usually have anonymous columns as well.

Still, in the outermost select (everything before the from), you should be able to do a split by comma and then look for either the last string "token" that is before the comma (and preceded by "as" or just whitespace), and failing that you can look for the "colname=" part as a second pass, and then as a failsafe take the first n chars of the raw expression and use that for your "anonymous" names.

This is how the query parsers work after all, so it's not impossible, but I wouldn't want to have to code it! The complexity required to cover any legal query is going to be daunting.

For giggles, try thinking of the parsing rules for just these variants:

select col1 as NOTCOL1, col2 from table

select col1 + 1, col3=col2 from table

with x as (
    select something from somewhere
)
select something as [Something with spaces for good measure] from x

select a.x, (b.col1) "look, a rainbow!"
from ( 
    select col1 as x
    from reused_table
) a
cross join reused_table b

Upvotes: 0

S3S
S3S

Reputation: 25112

You can find the column names of your talbes like so...

select t.name as TableName, c.Name as ColumnName
from sys.tables t 
inner join sys.columns c on c.object_id = t.object_id 
where t.name = 'yourTable'

So, you can wrap this in a cursor to do it for each table name, or just remove the WHERE clause to get it for ALL tables

Upvotes: 0

Related Questions