Getting List of Columns from a SQL Select statement

Question

Im wondering if there is anyway to get a list of the columns from a SQL Statement. Example, I have a simple SQL Statement like "Select FirstName, LastName From Customer", I want to get the list of the columns returned in the SQL Statement, which would be FirstName and LastName in this example. The SQL Statement is dynamic so it isnt known until runtime.

Currently I go about it by creating a view in the database on the fly than using:

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = @viewName

Than I drop the view.

This seems a bit dramatic for just getting the columns for the select statement.

Any ideas on how to go about this without creating and dropping the view?

Thanks in advance

Why don't you use string manipulation techniques to extract the column name from the query ? such as SQLQuery.substring... ? Do you need these column names inside sql server? — mpakbaz
– mpakbaz, Commented Oct 9, 2013 at 3:10
Hi mpakbaz, The problem with that is the combinations of sql before columns start is pretty huge. Also the sql definition will never be a perfect just name than comma, it could be a calculated field with an AS field mapping or a non-named column say EXPR1 type. — Matt
– Matt, Commented Oct 9, 2013 at 3:31
You don't know the columns in advance? What are you trying to do? — user172839
– user172839, Commented Oct 9, 2013 at 4:33
I would really like an answer to this. In our case we're troubleshooting the union of multiple with() statements and we need to match columns. if these were views we can do INFORMATION_SCHEMA but in the case of statements like these we've got no tool to introspect. — neydroydrec
– neydroydrec, Commented Jun 6, 2023 at 12:17

asantaballa · Accepted Answer · 2013-10-09 11:32:08Z

1

I often use a pretty hoaky technique where is do select with false condition like

Select ..... Where 1=0

This returns reasonably fast a result set with all the columns but no rows. So if running in a visual environment you can see what columns you have, and if running from a program/script all the normal column access facilities are available.

answered Oct 9, 2013 at 11:32

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe White · Accepted Answer · 2013-10-09 12:15:38Z

0

You should be able to use SET FMTONLY for this.

SET FMTONLY ON;
(run your query)
SET FMTONLY OFF;

When "format only" is on, SQL Server won't actually run the query; it just returns an empty resultset with the same format as the query would have had (i.e., all the columns, just no rows). Then you can investigate the columns' schema with whatever client API you're using (e.g. if you're using .NET, you can use SqlDataReader.FieldCount, GetFieldType, and GetName).

As noted on the documentation page I liked above, SET FMTONLY is deprecated in SQL Server 2012; so if you later upgrade your app to 2012, they recommend sp_describe_first_result_set instead.

answered Oct 9, 2013 at 12:15

Joe White

98.3k64 gold badges227 silver badges335 bronze badges

Collectives™ on Stack Overflow

Getting List of Columns from a SQL Select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related