T-SQL Tuesday #65: FMT_ONLY Replacements

TSQL2sDay150x150So, it’s the second Tuesday of the month and you know what that means! That’s right; T-SQL Tuesday #65 is upon us. This month’s topic is “Teach Something New” and is brought to us by Mike Donnelly (B|T). This T-SQL Tuesday is uniquely special in that it falls during the new SQL blogger challenge started by Ed Leightn-Dick (B|T). Though this topic is straight-forward, it was by no means an easy decision. The beautiful thing about SQL Server is regardless of how long you have been working with it, there is always something new you can learn.

If you’ve been working with SQL Server for a while, then you’ve probably heard about, or maybe even used, SET FMTONLY ON. This T-SQL statement was released in SQL Server 2005 and is used to return the metadata related to a T-SQL query. In 2012, this feature was replaced by two stored procedures, sp_describe_first_result_set and sp_describe_undeclared_parameters, and two Dynamic Management views (DMVs), dm_exec_describe_first_result_set and dm_exec_describe_first_result_set_for_object, that provide a lot more detail than FMTONLY does.

Let’s take a look at sp_describe_first_result_set and dm_exec_describe_first_result_set first since they are very similar. The syntax for each are as follows:

The first two arguments are fairly self-explanatory; the @tsql argument is where you specify the T-SQL statement, the @params argument is where you declare any parameters used in the statement, and the @include_browse_information or @browse_information_mode, depending on whether you are using the procedure or the DMV, is where you specify the depth of information you want returned. (I will use @include_browse_information from now on as they are interchangeable)

The @include_browse_information argument accepts three values: a value of zero will return basic information about the first result set including column ordinality, data type, length, etc.; a value of one returns everything that zero does along with base table information; and a value of two returns everything that the previous two values do along with any hidden rows used for computation. That’s not entirely correct. If the value of two is used and the query provided to the @tsql argument contains a view, then the view name will be returned instead of the base table information.

As you can see sp_describe_first_result_set and dm_exec_describe_first_result_set are very similar. The main difference between the two is that with the DMV, you can filter the results and specify the columns you want returned. This can provide greater flexibility in how you implement these.

The DMV dm_exec_describe_first_result_set_for_object is also similar to the procedure and DMV we just discussed. All three return the same result set, but dm_exec_describe_first_result_set_for_object takes two arguments, @object_id and @include_browse_information.

However, unlike the previous DMV and procedure, this DMV can only be used with stored procedures and triggers otherwise an error will be returned.

The last procedure sp_describe_undeclared_parameters is, in my opinion, an aptly-named procedure. This procedure takes two arguments and returns metadata about any undeclared parameters.

The @tsql argument is where you declare the parameterized tsql statement and the @params argument is where you declare the parameters in the statement. Any parameters in the @tsql argument that are not declared in the @params argument will have a row in the result set.

I have really enjoyed learning about these new DMVs and stored procedures, and I especially like that none these require any special privileges beyond the ability to execute the statement provided to the @tsql argument.


Leave a Reply

Your email address will not be published. Required fields are marked *