CODEWITHSUNDEEP
sqlsql-server-2008-r2
ChandlerPelhams
ChandlerPelhams

Reputation: 1678

Get Columns Returned from SQL Statement

Is there a way to get the columns that a SQL query would return without actually executing a SQL statement?

I looked into using set showplan_all on and using the OutputList field, but the results weren't quite what I wanted. I need to get the columns in the correct order and the correct column names (if they are aliased or not).

I am using SQL Server 2008 R2.

To clarify, here is an example of a query that could run:

--log that the user has executed a query
insert into execution_log_table
(timestamp
,user_id
,report_id)
values (CURRENT_TIMESTAMP
,1234
,5678)

select *
from (select column1
,column2
from another_table) tbl

I would not want to insert anything into the first table when trying to get the columns returned.

*Note: this is only a simple example, I have some SQL statements that are hundreds of lines of code that do multiple crud operations. I know that I could try to parse the lines of code manually, but my question was directed to a method using SQL servers parser to determine which columns would be returned in the final select statement.

Upvotes: 4

Views: 721

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

SET FMTONLY ON. It "returns" empty result sets, but shouldn't affect any actual tables:

set fmtonly on
go
insert into execution_log_table
([timestamp]
,user_id
,report_id)
values (CURRENT_TIMESTAMP
,1234
,5678)

select *
from (select column1
,column2
from another_table) tbl

By the way, remember to turn it off before using the same connection for any other activity (using SET FMTONLY OFF in its own batch), otherwise you can confuse yourself for a while. (As I did when trying to create tables to test your batch of statements, forgetting that the CREATE TABLEs themselves would fail silently (with success messages) once FMTONLY was on).

I've just noticed that this feature seems to be deprecated, and the replacement only allows you to retrieve information concerning the first result set. What use is that in the face of complex queries?

Upvotes: 1

Phil
Phil

Reputation: 43001

I don't think this is possible but you can certainly do something like

  select <column list>
    ... blah blah
  where 1=0

in order to get the shape of the result set without any results.

You have to execute a query but the execution time would be minimal.

Upvotes: 1

Related Questions