How to retrieve metadata for return values of an sql server stored procedure

Question

I want to analyze an SQL Server stored procedure from .NET code to retrieve metadata for the data/columns it returns (not OUTPUT parameters). Like when you drag/drop a sp on a DataSet i VisualStudio and it automatically generates columns. It could be useful for me for some code generation I'm testing out.

The procedures contains simple select statements like SELECT a,b FROM c and I'd like to get metadata about a and b.

Answer 1

You can use SET FMT_ONLY ON (and execute it) - but this is a bit hit-n-miss. It doesn't cover all scenarios (branches, etc) - and can still execute some code (extended stored procedures etc).

Alternatively - use table-valued-functions (udfs) - they have much richer/more-formal metadata.

Answer 2

Try this link http://blogs.rev-net.com/ddewinter/2008/11/09/getting-return-metadata-from-stored-procedures/