Getting Columns Headers without Result Data – SET FMTONLY ON

May 26, 2011 by pinaldave

I was recently watching a videos online of TechEd 2011 USA (link) and I learned that SET FMTONLY ON is going to be replaced with enhanced DMVs in future versions of SQL Server. I really liked the new direction of the product. However, SET FMTONLY ON is really have done its job so far. I have used it many times so far and always find it useful.

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. When this setting is ON the resultset only have headers of the results but no data. If resultset has Spatial Results, it will have the spatial results tab as well, however, no spatial data.

USE AdventureWorks2008R2
GOSET FMTONLY ON;SELECT *FROM HumanResources.Department;SELECT *FROM Person.Address;SET FMTONLY OFF;GO

If you have turned on the execution plan (CTRL+M) while executing this settings, it will not return any execution plan as well.

Reference: Pinal Dave (http://blog.SQLAuthority.com)