none
conn.GetSchema - How to retrieve schema data for a stored procedure RRS feed

  • Question

  • Hi there,

    I'm creating a O/R Mapping application and need to retrieve the column name, data type, ordinal position and such data for all the columns in the recordset returned by a stored procedure. I've managed to retrieve many schema information for my SQL Server 2005 databases but this one is giving me a hard time. Thanks in advance for your insight.

    Regards,

    -Benton
    Friday, April 24, 2009 3:18 PM

Answers

  • "to retrieve the column name, data type, ordinal position and such data for all the columns in the recordset returned by a stored procedure"...

    There is currently no clean feature to do this in SQL Server (we're actually working on making this better in next major version).   Note a stored procedure can return 0..N resultsets and all of them could be different resultsets.  Also, it can return a different number of resultsets and/or totally different results, number of columns, etc... based on parameter inputs.   Hence it's inherently a difficult problem to solve today.

    The "hacky" way people try to accomplish this today is use SET FMTONLY ON and execute sproc, examine what comes back, then SET FMTONLY OFF.  But this is not very easy to accomplish if the stored procedure has parameters what do you put in for the params (you just guess???).   You can also reverse engineer the TSQL by looking at sys.comments table (this is another technique, also very challenging).

    If I was doing this I would probably just give up right now knowing what I know. :)
    Monday, May 4, 2009 12:00 AM
    Moderator

All replies

  • I think you can query SQL Server system tables. Ask in a SQL Server forum for details.
    MSMVP VC++
    Friday, April 24, 2009 5:24 PM
  • You might also consider using the ADO.NET Entity Framework or LINQ to SQL.  These two frameworks both have O/R mapping features.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 30, 2009 5:59 PM
    Moderator
  • "to retrieve the column name, data type, ordinal position and such data for all the columns in the recordset returned by a stored procedure"...

    There is currently no clean feature to do this in SQL Server (we're actually working on making this better in next major version).   Note a stored procedure can return 0..N resultsets and all of them could be different resultsets.  Also, it can return a different number of resultsets and/or totally different results, number of columns, etc... based on parameter inputs.   Hence it's inherently a difficult problem to solve today.

    The "hacky" way people try to accomplish this today is use SET FMTONLY ON and execute sproc, examine what comes back, then SET FMTONLY OFF.  But this is not very easy to accomplish if the stored procedure has parameters what do you put in for the params (you just guess???).   You can also reverse engineer the TSQL by looking at sys.comments table (this is another technique, also very challenging).

    If I was doing this I would probably just give up right now knowing what I know. :)
    Monday, May 4, 2009 12:00 AM
    Moderator