You know I occasionally find it interesting how I learn new things. I was running through a practice test for the 70-461 and one of the questions had an odd command as one of the possible answers.

SET FMTONLY ON

Not something I had ever seen before, and as I always do when running through a practice test I marked it down as something to look into. After I was finished I went back and looked it up. It’s a somewhat interesting command that when turned on causes code not to be executed and returns only the column headings. However as of SQL 2012 BOL states:

Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL),sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Ok, so more research to do. I’ll say up front I have no idea why there is a sp_describe_first_result_set since it does exactly the same thing as sys.dm_exec_describe_first_result_set(). The others however turn out to be rather interesting. I’m not really going to go into sp_describe_undeclared_parameters right now since a) I didn’t see a huge use for it and b) the BOL entry was more than a little complicated. So what’s left? Sys.dm_exec_describe_first_result_set() and sys.dm_exec_describe_first_result_set_for_object().

They both do basically the same task, returning metadata. Sys.dm_exec_describe_first_result_set() returns the metadata for the first result set of a query. This can be a select, an EXEC statement, an INSERT with an OUTPUT statement, etc. Sys.dm_exec_describe_first_result_set_for_object() takes an object id of a stored procedure or trigger instead of a query and does the same thing.

My first thought was that I get similar information from sp_help. My second thought was a bit more interesting. Let’s say I have an insert statement that is getting a truncation error. I need to compare data types to figure out which column is giving me the truncation error. Historically I had two methods. One is to track back each column in my query to the original table, take into account any computations, and figure out what the data type is. But let’s say the query is more than a bit complicated .. one of those 2 pagers that you really don’t want to try to debug. At that point I could take the query to create a view and use sp_help. This works well but isn’t terribly elegant. Not to mention that it can have its own headaches, if, for example I have 50 columns and none of them are named then I have to go and manually name each one before I can create the query. Using sys.dm_exec_describe_first_result_set() I can just convert my query to a string (I do realize this can be a headache in and of itself.) and pass it to the function. I then get back all of the information I could have hoped for and more.

So here are a few examples of using these new DMVs:

SELECT * 
FROM sys.dm_exec_describe_first_result_set_for_object
(object_id('uspGetBillOfMaterials'),1)

SELECT * 
FROM sys.dm_exec_describe_first_result_set
('EXEC uspGetBillOfMaterials',NULL,1)

SELECT * 
FROM sys.dm_exec_describe_first_result_set
('SELECT * FROM Sales.vStoreWithContacts',NULL,1)

For a full list of the output columns you can go to BOL but here are a few of the things I noticed right off.

The basic data type information includes an is_nullable flag, the system_type_id, max_length, precision, scale and collation_name. To get the name of the data type you have to look at the system_type_name or user_type_database, user_type_schema and user_type_name respectively. This means that the data type name is split between standard types and user defined types. I could have wished that these were merged together and maybe had a flag stating that the type is user defined, however it is what it is, and even split up the information is very useful.

Next are some columns specifically with data about XML columns. Then a few more columns down we get to my favorites. Source information. These columns, when filled in, tell you the database, schema, table and column that the data is coming from. In order for these columns (and several others) to be filled in the 3rd parameter (@include_browse_information) has to be a 1. I ran some tests and found that I could get the source information for columns several layers of views deep. This is mind blowing for someone who has a legacy system with views that go 7 or 8 layers deep at times.

Remember that these DMOs are 2012 and up so you may have to wait to use them extensively (I know I will) but in the mean time I may have to get copies of a few of my legacy databases onto a 2012 instance.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, system functions, T-SQL