.net - TSQL - Parse Execution Plan to determine columns to be returned by a stored procedure

Question

Is there a way to dynamically determine (from .NET code or TSQL) the columns a stored procedure will return? I would like to dynamically generate wrapper functions in .NET for my stored procedures. It is easy to get proc names/parameters/etc. but I would also like to know what columns to expect when a procedure returns data (without executing that SP). Is this possible?

Answer 1

This is actually notoriously tricky. It works fine with UDFs, since they have stronger metadata - but a stored procedure can do lots of nasty things:

• branches (IF etc), returning completely different shapes
• execute another stored procedure
• execute dynamic sql

So very, very tricky. There are two common approaches:

• attempt to parse the TSQL; painful
• execute it with default (null, etc) values and check the result

The SET FMTONLY ON option is often used for the second (to avoid updates etc), but note that system stored procedures are still executed (perhaps xp_sendmail?), so you risk doing unwanted things...