Post reply

How to get Stored Proc return rowset metadata?

  • April 6, 2009 at 4:57 pm

    #72608

    I'm looking for a simple way to query the metadata of a stored procedure's returned rowset and get the data types of each column, without resorting to SMO or going outside SQL Server. I'm thinking of something like sp_help, but as far as I can tell neither sp_help or any other canned proc will give me what I need. SET FMTONLY ON will return only a blank rowset, but I can't figure out how to query it from T-SQL (for example, inside another stored proc or a query window.)

    Anybody know this? Seems like it should be simple.... 🙁

  • April 6, 2009 at 5:00 pm

    #972992

    So you want the column names and types in T-SQL.

    i.e, if I queried for metadata from the proc uspMyProc, you'd want something like

    name varchar(20)

    id int

    address varchar(20)

    in some result set?

  • April 6, 2009 at 5:07 pm

    #972994

    Yep, that would pretty much do it.

    Unfortunately, there doesn't seem to be any way to get directly to that information, unless I'm being incredibly dense (which is possible)....

  • April 6, 2009 at 7:03 pm

    #973023

    Stored procedures don't have fixed schema for their resultset.

    Same procedure may return different resultsets depending on its parameters, more than one recordset or no recordset at all. Some procedures may return results via output parameters.

    In fact, there is no way to tell what SP (in general) will return before the actual result is returned.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 4 (of 4 total)

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