Fmtonly statement in SQL Server

SET FMTONLY is used to set if the SQL Query should return the rows output to the client or only Meta Data (i.e. only columns and not the rows). If this statement is set with ON then we will get only the column headers and not the rows as output to your client. SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.

Syntex

SET FMTONLY ON/OFF

See the example below

If we use SET FMTONLY ON; then we can see the column only.

SET FMTONLY ON;

If we set OFF the we can see the rows.

By Default it is OFF.

Real time example where we can use this setting

An application or ETL tool needs to know the structure of the result set (column names & data types) of a complex query or stored procedure without actually running it (to avoid modifying data or long-running execution).

Below system function and sps is used to get the get the meta data.

Ø  sys.sp_describe_first_result_set

Ø  sys.dm_exec_describe_first_result_set

Ø  sys.dm_exec_describe_first_result_set_for_object

Ø  sys.sp_describe_undeclared_parameters

sys.sp_describe_first_result_set this SP is used to get the table meta data information see below.

EXEC sp_describe_first_result_set  @tsql = N'SELECT * FROM Production.Product'