SQL with Manoj

While running SQL Server Profiler to know what’s running behind the Application you might have noticed “SET FMTONLY ON” with other SQL statements. Most of you would be knowing this, the main point of putting this post is to let people know that this feature is deprecated in Denali and not to use it in new development work.

“SET FMTONLY ON” Returns only metadata to the client. It can be used to test the format of the response without actually running the query.
Let’s check how it is being used and what it returns:

USE AdventureWorks2012;
GO

-- Simple SELECT without using the "SET FMTONLY ON", OFF by-default:
SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

-- Using "SET FMTONLY":
SET FMTONLY ON;

SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

SET FMTONLY OFF;
GO

Output:

This feature is deprecated in Denali and should not be used in production code.

Now in replacement to this Denali has introduced 4 new objects to get the same information in easy and more informative way.
These are as follows:

--> 1. SP: sp_describe_first_result_set
-- Returns the metadata for the first possible result set of the Transact-SQL batch.
EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM HumanResources.Employee'

--> 2. SP: sp_describe_undeclared_parameters
-- Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.
EXEC sp_describe_undeclared_parameters @tsql = N'SELECT * FROM HumanResources.Employee where BusinessEntityID = @id AND BirthDate > @dt'

--> 3. DMF: sys.dm_exec_describe_first_result_set()
-- This is a DMF (Dynamic Management Function) which takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
SELECT * FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM HumanResources.Employee', NULL, 0)
-- It is similar to the SP sp_describe_first_result_set mentioned above.

--> 4. DMF: sys.dm_exec_describe_first_result_set_for_object()
-- This is a DMF (Dynamic Management Function) which takes an @object_id as a parameter and describes the first result metadata for the module with that ID.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('uspGetEmployeeManagers'), 0)
-- It has same set definition as sys.dm_exec_describe_first_result_set() DMF mentioned above.


-- The last mentioned DMF can also be used with the sys.procedures to get the metadata of the Stored Procedures:
SELECT p.name, X.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS X

… Just execute each one of these and check what all information they provide.

I really like these SPs and DM Functions, will be very useful in Metadata based based Dev work.

More info on this check MS BOL: http://msdn.microsoft.com/en-us/library/ms173839.aspx