Search code examples
.netsql-servercode-generationmetadata

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

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.

Solution

  • You can use SET FMTONLY 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.