Skip to content

SQLXpertise

Posts about SQL Server and SQL Azure

Tag: Meta Data Discovery

Meta Data Discovery Enhancements in SQL Server “Denali”


In Previous versions of SQL Server, to get the output format of a query or stored procedure, we should use SET FMTONLY ON/OFF

Let us see how SET FMTONLY works

USE AdventureWorks2008R2;
GO
SET FMTONLY ON;
GO
EXEC uspGetEmployeeManagers @BusinessEntityID=16
GO
SET FMTONLY OFF;
GO

Executing above query, returns the list of columns returned as output from the above SP

image

SQL Server “Denali” provides us 2 new System Stored procedures and 2 new DM Functions to returns this as query output

1. sp_describe_first_result_set

This system stored procedures returns the list of output columns from the first result set of passed stored procedure or query. Please note, If the SP returns multiple results set, this will provide us the output columns for first result set only

   1:  EXEC sp_describe_first_result_set @tsql= N'EXEC uspGetEmployeeManagers @BusinessEntityID=16'

Output:

image

2. sys.dm_exec_describe_first_result_set(@tsql nvarchar(MAX), @params nvarchar(max), @include_browse_information bit)

This DM Function works the same way as sp_describe_first_result_set, but you can use this to join with other queries easily

   1:  SELECT * FROM sys.dm_exec_describe_first_result_set(N'EXEC uspGetEmployeeManagers @BusinessEntityID=16', null, 0)

image

You can even use this to identify the output columns and its information for adhoc queries as well

   1:  SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT 1 + 1 [intA] UNION SELECT 2 [intB]', null, 0)

image

   1:  SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT DB_ID()', null, 0)

image

3. sys.dm_exec_describe_first_result_set_for_object(@object_id int, @include_browse_information bit)

This DM function accepts an object id as input parameter instead of T-SQL and returns the list of output columns from the first result set. The input object id can be Stored Procedure or Trigger’ object id

   1:  SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(N'uspGetEmployeeManagers'), 0)

image

How to get first result set’ output columns of all stored procedures with name contains ‘emp’

   1:  SELECT SPs.name, Results.column_ordinal, Results.name, Results.system_type_id, Results.system_type_name,
   2:      Results.max_length, Results.precision, Results.scale, Results.collation_name, Results.user_type_database, Results.user_type_schema
   3:      FROM sys.procedures AS SPs
   4:      CROSS APPLY sys.dm_exec_describe_first_result_set_for_object (SPs.object_id, 0) AS Results;
   5:      WHERE  SPs.name LIKE '%Emp%';

image

4. sp_describe_undeclared_parameters (@TSQL NVarchar(MAX), @Params NVarchar(Max))

This System stored procedures returns the result set of meta data about undeclared parameters specified the TSQL query passed as parameter

ex.

   1:  EXEC sp_describe_undeclared_parameters @tsql  = N'SELECT * FROM sys.databases WHERE database_ID > @DBID' 

 

The output of the above call returns us what is the possible data type for @DBID

image

I hope you will find this useful to get the meta data of stored procedures developed by others to find the output without executing it.

Share this:

  • Tweet
  • Email
  • Share on Tumblr
  • Print
  • Telegram
  • WhatsApp

Like this:

Like Loading...
Author ArunrajPosted on May 7, 2011May 8, 2011Categories SQL Server "Denali"Tags Meta Data Discovery, SQL Server, SQL Server "Denali" New Features, SQL Server DenaliLeave a comment on Meta Data Discovery Enhancements in SQL Server “Denali”

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 260 other followers

Top Posts & Pages

  • How to identify what features has been installed in your SQL Server ?
  • TRANSLATE()–Replace multiple characters in single function call
  • SQL Server Management Studio v 17.8.1 is released
  • How to access SQL Azure from anywhere or How to disable SQL Azure Firewall ?
  • How to import CSVs from Azure Blob Storage to SQL Server 2017 ?
  • How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared
  • PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1
  • How to backup your SQL Database and Upload to FTP or Cloud Automatically ?
  • STRING_AGG() – Concatenate values in Rows with Separator
  • Temporal Tables–Simple Guide to implement History Tables using SQL Server 2016/2017–Part 4–Managing Temporal Tables and History Data

Blogroll

  • SQL Blog
  • MS SQL Tips
  • SQL Server Performance
  • SQL Server Central
  • SQL Authority

Categories

  • .Net 2.0
  • ApexSQL Complete
  • Business Intelligence Projects
  • Docker
  • General
  • OData
  • Powershell
  • SQL Azure
  • SQL Azure Admin
  • SQL Azure Data Sync
  • SQL Azure Development
  • SQL Azure Management REST API
  • SQL Azure Migration
  • SQL Azure Reporting Services
  • SQL Backup and FTP
  • SQL Server
  • SQL Server "Denali"
  • SQL Server 2012
  • SQL Server 2012 Date Functions
  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2019
  • SQL Server Backup
  • SQL Server Cloud Backup
  • SQL Server Data Tools
  • SQL Server Diagnostics
  • SQL Server Encryption
  • SQL Server Intellisense
  • SQL Server on Linux
  • SQL Server Performance
  • Transaction Replication
  • Visual Studio 2012
  • Windows Azure

Archives

  • October 2018
  • September 2018
  • July 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • December 2017
  • November 2017
  • October 2017
  • April 2013
  • March 2013
  • January 2012
  • September 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011

  • Access Migration
  • Atlanta
  • Azure
  • Azure Blog Storage Backup
  • Azure SQL Database Managed Instance
  • DAC
  • DacImportExportCli
  • DACPAC
  • Data As Service
  • Denali
  • Meta Data Discovery
  • Migration Tools
  • MySQL Migration
  • MySQL to SQL Azure Migration
  • NJSQL User Group Presentation
  • OData
  • OData Explorer
  • PSSDiag
  • RAISERROR
  • Red Gate
  • Replication
  • SDCT
  • Sequences
  • SQL
  • SQL Azure
  • SQL Azure Admin
  • SQL Azure Backup
  • SQL Azure Bandwidth monitoring
  • SQL Azure Database Copy
  • SQL Azure Database Manager
  • SQL Azure Data Sync
  • SQL Azure Data Sync CTP2
  • SQL Azure Diagnostics
  • SQL Azure Firewall
  • SQL Azure Import Export
  • SQL Azure Labs
  • SQL Azure Migration
  • SQL Azure Monitoring
  • SQL Azure OData Service
  • SQL Azure Performance Tuning
  • SQL Azure Query Tool
  • SQL Azure Replication
  • SQL Azure Reporting Services
  • SQLDiag
  • SQL Diag Config Tool
  • SQL Diag Manager
  • SQL Diagnostics
  • SQL Nexus
  • SQL Server
  • SQL Server "Denali" New Features
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2017 on Linux
  • SQL Server 2017 on Ubuntu
  • SQL Server 2019
  • SQL Server Backup
  • SQL Server Cloud Backup
  • SQL Server Denali
  • SQL Server Diagnostics
  • SQL Server Encryption
  • SQL Server Management Studio
  • SQL Server Management Studio v 17.4
  • SQL Server on Linux
  • SQL Server Performance Tuning
  • SSMA for MySQL 5.0
  • T-SQL
  • TechEd
  • Tech Ed 2011
  • Temporal Tables
  • THROW
  • Transactional Replication
SQLXpertise Blog at WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: