SQL SERVER – Getting Columns Headers without Result Data – SET FMTONLY ON

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
GO
SET 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)

About these ads

17 thoughts on “SQL SERVER – Getting Columns Headers without Result Data – SET FMTONLY ON

  1. One time, at a client, I noticed that many events in the profiler were showing with suspiciously high amount of reads.
    I looked into that, and noticed that the bactches were preceded with FMTONLY statements, and in profiler both events had as much reads.
    At the time I was surprised, since the FMTONLY ON should have a few reads, since it just returns the metadata, not the entire resultset.
    Could you please explain what the processing routine of a FMTONLY statement looks like – i.e. is the statement parsed, algebrized, optimized and so on?

    Thank you.

  2. This is most stupid feature ever. ADO components sometimes add SET FMTONLY ON and make dry run.

    But FMTONLY ignore logical statements, so this is VALID part of code:

    CREATE PROCEDURE dbo.RunSomeCode(@Param VARCHAR(10))
    AS

    IF 1=2 — YEAH we are in FMTONLY
    BEGIN
    SET FMTONLY OFF
    — OR EXIT procedure
    END
    ..
    ..

    otherwise:

    1)

    SELECT
    a, b
    INTO
    #TABLE
    FROM
    SomeTable

    SELECT * FROM #Table -> FAIL, because #Table will not be created

    2)

    – @Foo = ”
    SET @mark = CHARINDEX(‘X’, @Foo)
    IF @mark > 0 — WITH FMTONLY this will pass
    BEGIN
    SET @SubStr = SUBSTRING(.. ) — Error here
    END

  3. Hi Pinal Also what do you think about this to get column names?
    SELECT * FROM Table WHERE 1=0

    I don’t have to mess with the SET OPTIONS and the execution plan is doing/does a constant scan ( I also tested it on a 10 TB table)

  4. Hi Pinal, I Am Not Understanding That What’s Use Of It In Practical Scenario. In Which Situation It’s Helpful to The Developer also?

  5. Hi Pinal – I found it very useful to get the ADO.NET DataSet Schema fill to work – and the LINQ2SQL designer to work – where I have used a temporary table in a stored procedure. In this case you may get an error message saying the temporary table could not be found – after some digging around I came across the FMTONLY option and hence your post.

    I found that if I set the set fmtonly off; just before I created a temporary table and set fmtonly on; just after I created the table then the schema could be read quite happily (I had joined on the temporary table for the final SELECT) – with both ADO.NET DataSets and the LINQ2SQL designer. Apparently the ADO.NET and LINQ tools uses the FMTONLY option internally against the entire stored procedure – my fix just overrode it enough to allow temp table to be parsed as well.

    • To expand a bit on what Paul said,

      If you’re using an ORM mapper (we use ADO.NET entity framework) sometimes it will not be able to see the result set properly when you’re trying to setup your mappings. If you watch it in profiler, the framework will open a connection to the DB, SETS FMTONLY ON, then runs the proc with all NULL values passed in for the parameters.

      It works most of the time, but I occasionally Entity Framework will just report an empty result set. I haven’t seen any pattern to my SQL code when this happens, but the only fix I’ve come across so far is to add SET FMTONLY OFF within the procedure’s definition and redeploy the SQL code.

  6. Hi Pinal, is there a way I could get a “vertical” list of columns..I am interested in just the number of columns being returned..It’s like saying “Is there an equivalent of count(*) to count the number of columns returned by a stored procedure”?

    • Select into a temp table, say #mytemp, and do this:

      SELECT COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ‘#mytemp%’

      And you will get a vertical list of the column names. You can add metadata or whatever else from INFORMATION_SCHEMA as well, of course.

  7. Hi pinal , Inside set FMT ony on does not allow Go Statement .What i do
    For ex:
    Set Fmt only on

    Select * from Tables1

    Go

    Update table1 set id =@id where name =@name

    Go

    I got Error .what i do

  8. Pingback: SQL SERVER – Copy Column Headers from Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s