How to get result row column definitions without executing query

Question

Post reply

How to get result row column definitions without executing query

Robb Murdock

Valued Member

Points: 53
More actions
November 12, 2005 at 11:16 am

#167010

Greetings:
I am designing a system that needs to know the column names, length and datatypes of the result row from the SQL statement or stored procedure that is about to be executed.
QUESTION:
Is there some way to 'ask' SQLServer to parse the SQL Statement but not execute it; and get back all the column names, lengths and datatypes of the result row that would have been returned had the SQL Statement been executed ?
Any insight would be greatly appreciated.
Thanks,
Robb

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sergey Barskiy SSC Rookie Points: 37 More actions · Answer 1

I would user "where 1=0". THis should provide instant reply with all teh columsn listed but no rows.

Robb Murdock Valued Member Points: 53 More actions · Answer 2

Sergey:

Yes, that would work, but I won't have the ability to touch the SQL. I am building a wrapper around ADO that instead of running the query will schedule it to be run later and place the results into a holding table. I need to know the column names and datatype so as to create the holding table.

It seems like, under the covers, SQL knows the column names perhaps before actually executing the query because sometimes when I break a long running query from the QueryAnalyzer, it returns an empty result showing the column names.

Any other ideas ?

Thanks for your reply.

Robb

Robb Murdock Valued Member Points: 53 More actions · Answer 3

Sergey:

Yes, that would work, but I won't have the ability to touch the SQL. I am building a wrapper around ADO that instead of running the query will schedule it to be run later and place the results into a holding table. I need to know the column names and datatype so as to create the holding table.

It seems like, under the covers, SQL knows the column names perhaps before actually executing the query because sometimes when I break a long running query from the QueryAnalyzer, it returns an empty result showing the column names.

Any other ideas ?

Thanks for your reply.

Robb

Mike Kendall SSC Eights! Points: 958 More actions · Answer 4

Robb,

I posed a similar question a short while back. The answer I got makes sense, even though it wasn't what I was hoping for. Consider the following:

CREATE PROCEDURE test

@p1 int = 0

AS

If @p1 = 0

SELECT col01,col02,col03 FROM tbl01

Else

SELECT fld99,fld98,fld97,fld96 FROM tbl02

Bottom line - The column list "Depends"

Mike

TimS Ten Centuries Points: 1165 More actions · Answer 5

SET FMTONLY ON

GO

SELECT *

FROM dbo.sysobjects

GO

Tim S

Robb Murdock Valued Member Points: 53 More actions · Answer 6

Thanks to all that replied, but I don't think I made my issue clear enough.

When I process a SQL Select, it could be simply an EXEC <stored proc name> or it could be a complex SQL statement with tons of inner and outer joins and unions etc.

It is unlikely that the query I will be processing is a simple select of a single fixed table. Hence there is nothing in dbo.sysobjects that will identify what the result row will look like once one of these queries is finished.

Technically, I could potentially parse the complex SQL statement looking for returned column names; but I wouldn't have the datatype info. Additionally, the SQL statement could be including wildcard data from some joined table; or be executing a proc that returns some result set.

I believe that it may be impossible to know the final result set definition without actually executing the statement. Still, I can't help thinking that SQL knows since I have seen the QueryAnalyzer return an empty result row after I have stopped a long-running, complex query before it finished.

Thanks again for your help.

Robb

TimS Ten Centuries Points: 1165 More actions · Answer 7

TimS

Ten Centuries

Points: 1165

November 15, 2005 at 5:03 pm

#604061

Did You Try it!!!!!!

SET FMTONLY ON

Tim S

TimS Ten Centuries Points: 1165 More actions · Answer 8

SET FMTONLY

Returns only meta data to the client.

Syntax

SET FMTONLY { ON | OFF }

Remarks

No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON.

The setting of SET FMTONLY is set at execute or run time and not at parse time.

Permissions

SET FMTONLY permissions default to all users.

Examples

This example changes the SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.

SET FMTONLY ON

GO

USE pubs

GO

SELECT *

FROM pubs.dbo.authors

GO

Robb Murdock Valued Member Points: 53 More actions · Answer 9

Tim S:

Yes, I did try it. Thanks!

It will fail if the proc being executed creates a temp table. If it's a straight SQL statement, it seems to work; but if I can't get the result set from a proc then it won't work for me.

THanks again for your suggestion.

Robb