Search results matching tags 'SQL Server 11', 'metadata discovery', and 'SQL 11'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+11,metadata+discovery,SQL+11&orTags=0Search results matching tags 'SQL Server 11', 'metadata discovery', and 'SQL 11'en-USCommunityServer 2.1 SP2 (Build: 61129.1)SQL Server v.Next (Denali) : Metadata enhancementshttp://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspxMon, 20 Dec 2010 15:09:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:31328AaronBertrand
<p>In my previous job, we had several cases where schema changes or incorrect developer assumptions in the middle tier or application logic would lead to type mismatches. &nbsp;We would have a stored procedure that returns a BIT column, but then change the procedure to have something like CASE WHEN &lt;condition&gt; THEN 1 ELSE 0 END. &nbsp;In this case SQL Server would return an INT as a catch-all, and if .NET was expecting a boolean, BOOM. &nbsp;Wouldn't it be nice if the application could check the result set of the stored procedure, and construct its data types using that information? &nbsp;Another case would be where the schema has changed, but the applications can't all be updated at once. &nbsp;Wouldn't it be great to be able to tell SQL Server what the column name should be, what data type you want, or in what collation, as part of the stored procedure call? &nbsp;How often have you cursed the bizarre behavior of SET FMTONLY ON? &nbsp;And finally, how many times have you been frustrated by the output of sp_who2, which returns SPID twice - and worse yet, as a CHAR(5) column instead of an INT?&nbsp;</p>
<p>You can deal with each of these scenarios with some new procedures and dynamic management functions introduced&nbsp;in Denali. &nbsp;We will have new functionality to either discover or control the metadata of queries, without actually having to run them, store temporary results elsewhere, or use SET FMTONLY ON.<br><br></p>
<p><font size="4">Some examples</font></p>
<blockquote>
<p><b>Inspect output of queries without piecing together catalog views</b></p>
<p>Picture a very simple table like this: <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE TABLE </font><font color="black">dbo.x<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">foo&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">INT </font><font color="gray">NOT NULL,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">bar&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">DECIMAL</font><font color="gray">(</font><font color="black">12</font><font color="gray">, </font><font color="black">2</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">blat&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">mort&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">splunge </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">whence&nbsp; </font><font><font color="blue">DATETIME2</font></font><font color="gray">(</font><font color="black">4</font><font color="gray">) NOT NULL<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>And then a query like this:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="gray">* </font><font color="blue">FROM </font><font color="black">dbo.x</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Today, if I want the application to understand the data types that are coming back from this query, I would have to create queries against the catalog views sys.columns and sys.types, for example:<br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">t.name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.max_length</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.[precision]</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.scale</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.is_nullable<br></font><font color="blue">FROM <br>&nbsp;&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">columns</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">c<br></font><font color="blue">INNER JOIN <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">types</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">t<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ON </font><font color="black">c.system_type_id = </font><font color="blue"></font><font color="black">t.system_type_id<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">AND </font><font color="black">c.user_type_id = </font><font color="blue"></font><font color="black">t.user_type_id<br></font><font color="blue">WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.[object_id] =</font><font color="blue"> </font><font color="magenta">OBJECT_ID</font><font color="gray">(</font><font color="red">'dbo.x'</font><font color="gray">)<br></font><font color="blue">ORDER BY<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.column_id</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<p>&nbsp;<img src="http://sqlblog.com/files/folders/31746/download.aspx" width="561" border="1" height="159"></p>
</blockquote>
<p>This leaves some serious gaps, since I have to perform all kinds of manipulations to put these outputs into true data type definitions.&nbsp; For example, I have to check max_length for -1 and switch it to MAX, I have to check for nchar/nvarchar and cut max_length in half, and I have to piece together the precision and scale for *some* numeric- and date-based types.</p>
<p>There is a new dynamic management function in Denali, sys.dm_exec_describe_first_result_set, which will take a query and produce a much more concise description of the data types coming back in the query.&nbsp; While the function returns other columns, I'll focus on the ones that are most useful for describing resultsets and creating new tables based on them.</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">system_type_name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">is_nullable<br></font><font color="blue">FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set</font></font><font color="black"><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="red">N'SELECT * FROM dbo.x;'</font><font color="gray">, NULL, </font><font color="black">0<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">) </font><font color="blue">AS </font><font color="black">f<br></font><font color="blue">ORDER BY<br> </font><font color="black">column_ordinal</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31747/download.aspx" width="350" border="1" height="160">&nbsp;</p>
</blockquote>
<p>Clearly this is a lot less work and manipulation to get the data types consistent - all of the "compound" data types come back exactly as you need to define them.&nbsp; So let's imagine you want to generate CREATE TABLE scripts to store the data from a bunch of DMVs or your own views or other queries; again, you can do this without writing very complex queries against the catalog views or with piecing together the data types manually.&nbsp; A very simple example that generates a CREATE TABLE statement to store the data from sys.dm_exec_sessions in your own repository table:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SET NOCOUNT ON</font><font color="gray">;<br><br></font><font color="blue">DECLARE </font><font color="#434343">@sql </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">);<br></font><font color="blue">SET </font><font color="#434343">@sql = </font><font color="blue"></font><font color="red">N'SELECT * FROM sys.dm_exec_sessions;'</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="red">'CREATE TABLE dbo.SessionsDMV('</font><font color="gray">;<br><br></font><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="magenta">CASE </font><font color="black">column_ordinal <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHEN </font><font color="black">1 </font><font color="blue">THEN </font><font color="red">'' </font><font color="blue">ELSE </font><font color="red">',' </font><font color="blue">END <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">+ </font><font color="black">name </font><font color="gray">+ </font><font color="red">' ' </font><font color="gray">+ </font><font color="black">system_type_name </font><font color="gray">+ </font><font color="magenta">CASE </font><font color="black">is_nullable <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHEN </font><font color="black">0 </font><font color="blue">THEN </font><font color="red">' not null' </font><font color="blue">ELSE </font><font color="red">'' </font><font color="blue">END<br>FROM <br>&nbsp;&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set</font></font><br><font color="black">&nbsp;&nbsp;&nbsp; </font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@sql</font><font color="gray">, NULL, </font><font color="black">0<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">) </font><font color="blue">AS </font><font color="black">f<br></font><font color="blue">ORDER BY<br> </font><font color="black">column_ordinal</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="red">');'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>The results (not exactly how I would want them formatted, but you can play with the query above so the code conforms better to your standards):</p>
<p>&nbsp;</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">-----------------------------<br>CREATE TABLE dbo.SessionsDMV(<br><br>------------------------------------------------------------------<br>session_id smallint not null<br>,login_time datetime not null<br>,host_name nvarchar(128)<br>,program_name nvarchar(128)<br>,host_process_id int<br>,client_version int<br>,client_interface_name nvarchar(32)<br>,security_id varbinary(85) not null<br>,login_name nvarchar(128) not null<br>,nt_domain nvarchar(128)<br>,nt_user_name nvarchar(128)<br>,status nvarchar(30) not null<br>,context_info varbinary(128)<br>,cpu_time int not null<br>,memory_usage int not null<br>,total_scheduled_time int not null<br>,total_elapsed_time int not null<br>,endpoint_id int not null<br>,last_request_start_time datetime not null<br>,last_request_end_time datetime<br>,reads bigint not null<br>,writes bigint not null<br>,logical_reads bigint not null<br>,is_user_process bit not null<br>,text_size int not null<br>,language nvarchar(128)<br>,date_format nvarchar(3)<br>,date_first smallint not null<br>,quoted_identifier bit not null<br>,arithabort bit not null<br>,ansi_null_dflt_on bit not null<br>,ansi_defaults bit not null<br>,ansi_warnings bit not null<br>,ansi_padding bit not null<br>,ansi_nulls bit not null<br>,concat_null_yields_null bit not null<br>,transaction_isolation_level smallint not null<br>,lock_timeout int not null<br>,deadlock_priority int not null<br>,row_count bigint not null<br>,prev_error int not null<br>,original_security_id varbinary(85) not null<br>,original_login_name nvarchar(128) not null<br>,last_successful_logon datetime<br>,last_unsuccessful_logon datetime<br>,unsuccessful_logons bigint<br>,group_id int not null<br>,authenticating_database_id int<br><br>----<br>);</pre></td>
</tr>
</table>
</blockquote>
<p>Now, you can copy the entire result from the messages pane, and run it to create a table that will accept an INSERT statement like "INSERT dbo.SessionsDMV SELECT * FROM sys.dm_exec_sessions;"</p>
<p>I'll leave it as an exercise to the reader to try this out against different values of @sql.</p>
<p><br><b>Best guess at undeclared parameters</b>&nbsp; </p>
<p>There is also a new stored procedure that will allow you to inspect a T-SQL batch and determine the data types of any undeclared parameters.&nbsp; As an example, what are the data types of the variables that will ultimately be passed into this query text?&nbsp; (This is often something an application developer will have to determine in order to program around these queries.)</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="gray">* </font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">objects</font></font><font color="black"> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHERE </font><font color="black">[object_id] =</font><font color="blue"> </font><font color="#434343">@ObjectID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">OR (</font><font color="black">name </font><font color="gray">LIKE </font><font color="#434343">@ObjectName</font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>On first glance, most of us will know that the first parameter, @ObjectID, should be declared as an INT, and the second parameter is likely going to be an NVARCHAR(4000).&nbsp; However there is now way for the system to tell us these exact answers with a lot less uncertainty:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_describe_undeclared_parameters<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@tsql =</font><font color="blue"> </font><font color="red">N'SELECT * FROM sys.objects <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE [object_id] = @ObjectID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OR (name LIKE @ObjectName);'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Partial results:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31748/download.aspx" width="552" border="1" height="68">&nbsp;</p>
</blockquote>
<p>This returns a lot of other columns as well, but even the above should show how the stored procedure will be able to examine large and complicated T-SQL batches and describe all of the parameters that are used.&nbsp; Now, I say "best guess" because the parameter inspection is not perfect - but for most cases it is pretty spot on.<br></p>
<p><br><b>Convert data types explicitly</b></p>
<p>In this example I am showing a simple stored procedure.&nbsp; If we have two applications that call this stored procedure, we can call the procedure with two different WITH RESULT SETS options in order to return the data differently for the applications, allowing us to slowly change schema across our applications instead of changing them all at once.&nbsp; Imagine the following batch where the first column name is changing from 'Email' to 'Username' (the system no longer requires an e-mail address as the user identifier) and the second column, 'Status,' is changing from an INT to a BIT.&nbsp; The current procedure looks like this:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Username</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] =</font><font color="blue"> </font><font color="magenta">CASE </font><font color="blue">WHEN </font><font color="black">IsActive = </font><font color="blue"></font><font color="black">1 </font><font color="blue">THEN </font><font color="black">1 </font><font color="blue">ELSE </font><font color="black">0 </font><font color="blue">END<br>&nbsp;&nbsp;&nbsp;&nbsp;FROM </font><font color="black">dbo.Users<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ORDER BY </font><font color="black">Username</font><font color="gray">;<br></font><font color="blue">END<br>GO</font><font color="black"></font></pre></td>
</tr>
</table>
</blockquote>
<p>As we prepare for the schema changes, we could change the code above to just say:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Email =</font><font color="blue"> </font><font color="black">Username</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] = </font><font color="blue"></font><font color="magenta">CONVERT</font><font color="gray">(</font><font><font color="blue">BIT</font></font><font color="gray">, </font><font color="magenta">CASE<br></font><font color="blue"> WHEN </font><font color="black">IsActive =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">&lt;</font><font color="black">and other conditions</font><font color="gray">&gt; </font><font color="blue">THEN </font><font color="black">1<br></font><font color="blue"> ELSE </font><font color="black">0 </font><font color="blue">END</font><font color="gray">)<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.Users<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ORDER BY </font><font color="black">Email</font><font color="gray">;<br></font><font color="blue">END<br>GO<br></font></pre></td>
</tr>
</table>
</blockquote>
<p>But of course unless we update all of our apps at the same time, this change will break at least one of them.&nbsp; Instead, we can use WITH RESULT SETS to change the shape of the resultset conditionally.&nbsp; From the application where we can change the app immediately to use the new column names, we can say:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Email </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">320</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] </font><font><font color="blue">BIT</font></font><font color="black"><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>In the results, you can't really tell from the screen shot whether Status is a BIT or an INT, but you can certainly see that the Username column has been changed to Email without changing the stored procedure and without having to change all of the consuming apps at once:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31745/download.aspx" width="289" border="1" height="316">&nbsp;</p>
</blockquote>
The old application can remain unchanged, and continue calling the stored procedure in the default manner - it will still see the Username column, and it will still get Status back as an Int32 instead of a Boolean (we are talking .Net data types here, not SQL).&nbsp; If there really are only two applications, then the WITH RESULT SETS code can be removed from the first application when the schema has changed and the second application has been updated.&nbsp; Otherwise, each app can make use of WITH RESULT SETS until they are all synchronized.&nbsp; At my previous job, I could have used this technique in many scenarios.</blockquote>
<blockquote><br><b>Fix output of system stored procedures</b></blockquote>
<blockquote>Every time I run sp_who2, I cringe. There are two SPID columns for some reason, and both are CHAR(5).&nbsp; Arguably, these could be made SMALLINT, but I think INT plays better with the .NET layer in most cases.&nbsp; There is also a column called BlkBy, which - unlike the rest of the columns - has a name which is unnecessarily truncated.&nbsp; I'll leave this as a CHAR(5) because I know the "." result is appreciated by a lot of people (it lets the true numeric values stand out better than 0 or NULL would).&nbsp; That all said, here is an example of using WITH RESULT SETS to make the output more consumable:
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="black">[master]..</font><font color="darkred">sp_who</font><font color="black">2<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[SPID]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">INT</font><font color="gray">,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- fixed the data type<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Login]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">HostName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Blocker&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">CHAR</font><font color="gray">(</font><font color="black">5</font><font color="gray">),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- renamed this column<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Database]&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Command&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">CPUTime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">30</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">DiskIO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">30</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">LastBatch&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">48</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">ProgramName&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">255</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Redundant_SPID </font><font color="blue">INT</font><font color="gray">,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- renamed this column<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">RequestID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">INT<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p><b><br>Make more usable output in Management Studio</b></p>
<p>In Denali there is a new system stored procedure called <a href="http://msdn.microsoft.com/en-us/library/ff878233%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878233(SQL.110).aspx" target="_blank">sys.sp_server_diagnostics</a>, which returns some core system health metrics in XML format.&nbsp; Unfortunately, the implementation returns this data as string rather than XML data, which means the output is pretty useless in Management Studio - you either have to expand the result column and scroll horizontally forever in results to grid mode, or do all kinds of juggling to figure out where each row ends in results to text mode.&nbsp; (I complained about this in <a href="http://connect.microsoft.com/SQLServer/feedback/details/625262/denali-engine-sys-sp-server-diagnostics-should-return-xml-data-as-xml" title="http://connect.microsoft.com/SQLServer/feedback/details/625262/denali-engine-sys-sp-server-diagnostics-should-return-xml-data-as-xml" target="_blank">Connect #625262</a>, but they didn't seem to agree with my reasoning - though I'm curious what other data type they think the output may change to in the future.)&nbsp; In order to make the output more useful, we can do the following to convert the last column to XML, making it clickable within SSMS grid results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font><font color="green">sys</font></font><font color="black">.</font><font color="black"></font><font color="darkred">sp_server_diagnostics<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">create_time&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">DATETIME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">component_name </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">)</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[state]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">state_desc&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">XML<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>In the results, we can now click on the data column in any row, and it will open a new XML document that is much easier to read and parse: </p>
<blockquote><img src="http://sqlblog.com/files/folders/31744/download.aspx" width="687" border="1" height="158"><br> <br></blockquote>
<p><b>Changing the output collation</b></p>
<p>This one is late to the party but since publishing I felt this nagging feeling that the article was incomplete.&nbsp; I can't think of an obvious and practical use for this off the top of my head, as thankfully I have not been exposed to too many collation issues over the years.&nbsp; But if a query is outputting a column in a certain collation, you can override that collation using WITH RESULT SETS.&nbsp; Here is an example that changes Albanian_BIN to Albanian_100_BIN, and I pass this batch to the DMV just to show that the output collation is obeyed:<br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">DECLARE </font><font color="#434343">@sql </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">);<br><br></font><font color="blue">SET </font><font color="#434343">@sql = </font><font color="blue"></font><font color="red">N'EXEC(''SELECT N''''foo'''' COLLATE Albanian_BIN'')<br>&nbsp;&nbsp;WITH RESULT SETS<br> (<br> (<br> foo NVARCHAR(32) COLLATE Albanian_100_BIN<br> )<br> );'</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="black">name</font><font color="gray">, </font><font color="black">collation_name<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set<br> </font></font><font color="gray">(<br> </font><font color="#434343">@sql</font><font color="gray">, NULL, </font><font color="black">0<br> </font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote><img src="http://sqlblog.com/files/folders/31852/download.aspx"><br></blockquote>
<p>&nbsp;</p>
<p><b>Easier translation in SSIS</b></p>
<p>Finally, here is a blog post from fellow MVP James Rowland-Jones, where he demonstrates how EXECUTE ... WITH RESULT SETS can be very handy in SSIS: <br></p>
<blockquote>
<p><a href="http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/11/16/denali-using-execute-with-result-sets-example-using-ssis-source-adaptors.aspx" title="http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/11/16/denali-using-execute-with-result-sets-example-using-ssis-source-adaptors.aspx" target="_blank">Denali - Using EXECUTE WITH RESULT SETS (example using SSIS Source Adaptors)</a> <br></p>
</blockquote>
</blockquote>
<p><br><font size="4">Caveats</font> <br></p>
<p>The DMVs and stored procedures for describing result sets are currently written to only inspect the *first* result set.&nbsp; So for something like sp_help, it will only assist in determining the very first set of output.&nbsp; I suspect they will correct this in the future, and am thankful they named the objects in an unambiguous way.</p>
<p>In addition, there are certain types of batches that will simply return an error when you try to examine them.&nbsp; For example, while a batch with a table variable can be passed to sys.sp_describe_first_result_set, a batch with a temp table cannot: <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_describe_first_result_set <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@tsql =</font><font color="blue"> </font><font color="red">N'CREATE TABLE #y (j INT);SELECT j FROM #y;'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1<br>The metadata could not be determined because statement 'SELECT j FROM #y;' uses a temp table.</div></td>
</tr>
</table>
</blockquote>
<p>WITH RESULT SETS requires a double-nesting of parentheses.&nbsp; If you try the following:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'';'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br></font><font color="green">-- (<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">foo </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">3</font><font color="gray">)<br></font><font color="green">-- )<br></font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 102, Level 15, State 1, Line 4<br>Incorrect syntax near 'foo'.</div></td>
</tr>
</table>
</blockquote>
<p>To get around this, you will need to un-comment the commented parentheses.&nbsp; The reason is that WITH RESULT SETS is plural; you can dictate the shape of multiple result sets, as demonstrated above.<br></p>
<p>Also note that you can't use WITH RESULTS to add or remove columns from the output; you can only mess with the column names, data types, nullability and collation.&nbsp; So for example, if you try to eliminate columns from the result set as follows:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'', x = 1;'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(</font><font color="black">foo </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">3</font><font color="gray">))<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results: </p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 11537, Level 16, State 1, Line 1<br>EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.</div></td>
</tr>
</table>
</blockquote>
<p>And if you try to convert to an incompatible data type, it will fail at runtime.&nbsp; For example, the following code will compile, but if you run it:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'';'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(</font><font color="black">foo </font><font color="blue">INT</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 8114, Level 16, State 2, Line 1<br>Error converting data type varchar to int.</div></td>
</tr>
</table>
</blockquote>
There are many other limitations and caveats to using these metadata enhancements; if you can see usefulness in some of these capabilities, I strongly recommend reviewing the official documentation and playing with the features for yourself.<br>
<p><br><font size="4">Official links</font></p>
<p>Here are links to the official documentation about these new features: <br></p>
<blockquote>
<p>sys.sp_describe_first_result_set<br><a href="http://msdn.microsoft.com/en-us/library/ff878602%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878602(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878602(SQL.110).aspx</a></p>
<p>sys.sp_describe_undeclared_parameters<br><a href="http://msdn.microsoft.com/en-us/library/ff878260%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878260(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878260(SQL.110).aspx</a></p>
<p>sys.dm_exec_describe_first_result_set_for_object<br><a href="http://msdn.microsoft.com/en-us/library/ff878236%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878236(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878236(SQL.110).aspx</a><br><br>sys.dm_exec_describe_first_result_set<br><a href="http://msdn.microsoft.com/en-us/library/ff878258%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878258(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878258(SQL.110).aspx</a><br></p>
<p>EXECUTE<br><a href="http://msdn.microsoft.com/en-us/library/ms188332%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx</a></p>
</blockquote>
<br>