As many of you (should) know, you can select result sets from stored procedures to return them to your application, but there is no built-in metadata that tells you the specifications of the columns in each result set. However, I was always intrigued by the way the LINQ to SQL designer managed to determine, without flaw, the names, data types, etc. of the columns returned by stored procedures.

So to satisfy my curiosity I set up a trace using the SQL Server profiler against the database on my box and played with the LINQ to SQL designer to get it to find that metadata. Much to my surprise, it calls the stored procedure directly, passing NULL for each of its parameters.

Wait a minute; my stored procedure does validation, so it will raise an error when xyz parameter is NULL. I then noticed earlier in the trace a strange statement that involved the SET FMTONLY statement. Searching for that statement revealed this gem.

"Returns only metadata to the client. Can be used to test the format of the response without actually running the query."

Running something like this would then yield the metadata of the result set(s), regardless of whether errors are raised.

The nice thing is that you can do this for any query, not just stored procedure executions.