SA0210 : Setting FMTONLY option is deprecated

The SET FMTONLY option, when used in T-SQL scripts on SQL Server, returns only metadata about the result set without actually executing the query. This can lead to unexpected results or behaviors, especially in stored procedures and dynamic SQL, because the actual execution logic is bypassed.

For example:

1-- Problematic use of SET FMTONLY
2SET FMTONLY ON;
3SELECT * FROM Employees;
4SET FMTONLY OFF;

This example does not run the SELECT statement against the Employees table; instead, it only retrieves column information, which can cause discrepancies in applications expecting full execution.

• Can lead to incorrect assumptions about data if queries are expected to fetch and process records.

• May cause application errors as logic that relies on the actual data execution is skipped.

To resolve issues related to the usage of SET FMTONLY, replace it with modern alternatives that safely retrieve metadata while ensuring proper query execution.

Follow these steps to address the issue:

1. Identify occurrences of SET FMTONLY in your T-SQL scripts or stored procedures.

2. Replace instances of SET FMTONLY with sp_describe_first_result_set or sys.dm_exec_describe_first_result_set to retrieve metadata correctly without executing the query logic. These alternatives provide detailed metadata information safely.

3. Test the changes to ensure they produce the expected results and that all application functionalities depending on these scripts are working correctly.

For example:

1-- Instead of using SET FMTONLY
2-- Problematic code
3-- SET FMTONLY ON;
4-- SELECT * FROM Employees;
5-- SET FMTONLY OFF;
6
7-- Use this modern alternative to retrieve metadata
8EXEC sp_describe_first_result_set N'SELECT * FROM Employees';

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Deprecated Features, Bugs

Deprecated Database Engine Features in SQL Server 2017

Other Resources