Post reply

How to get result row column definitions without executing query

  • 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

  • November 12, 2005 at 1:43 pm

    #603555

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

  • November 12, 2005 at 4:52 pm

    #603564

    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

  • November 12, 2005 at 4:53 pm

    #603565

    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

  • November 15, 2005 at 1:14 pm

    #604026

    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

     

     

  • November 15, 2005 at 3:04 pm

    #604050

    SET FMTONLY ON

    GO

    SELECT *

    FROM dbo.sysobjects

    GO

    Tim S

  • November 15, 2005 at 3:16 pm

    #604052

    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

  • November 15, 2005 at 5:03 pm

    #604061

    Did You Try it!!!!!!

    SET FMTONLY ON

    Tim S

  • November 15, 2005 at 5:12 pm

    #604062

    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

  • November 15, 2005 at 5:16 pm

    #604064

    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

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

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