SET FMTONLY (Transact-SQL)SET FMTONLY (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

只将元数据返回给客户端。Returns only metadata to the client. 可以用于测试响应的格式,而不必实际执行查询。Can be used to test the format of the response without actually running the query.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SET FMTONLY { ON | OFF }   

RemarksRemarks

FMTONLYON 时,返回包含列名称但不含任何数据行的行集。When FMTONLY is ON, a rowset is returned with the column names, but without any data rows.

分析 Transact-SQL 批时,SET FMTONLY ON 无效。SET FMTONLY ON has no effect when the Transact-SQL batch is parsed. 在执行运行时期间生效。The effect occurs during execution run time.

默认值是 OFFThe default value is OFF.

权限Permissions

要求具有 public 角色的成员身份。Requires membership in the public role.

示例Examples

以下 Transact-SQL 代码示例将 FMTONLY 设置为 ONThe following Transact-SQL code example sets FMTONLY to ON. 此设置使 SQL Server 仅返回有关所选列的元数据信息。This setting causes SQL Server to return only metadata information about the selected columns. 具体来说,返回列名称。Specifically, the column names are returned. 不返回任何数据行。No data rows are returned.

在该示例中,存储过程 prc_gm29 的测试执行返回以下内容:In the example, the test execution of stored procedure prc_gm29 returns the following:

  • 多个行集。Multiple rowsets.
  • 来自多个表的列,位于其某个 SELECT 语句中。Columns from multiple tables, in one of its SELECT statements.
go
SET NoCount ON;

go
DROP PROCEDURE IF EXISTS prc_gm29;

DROP Table IF EXISTS #tabTemp41;
DROP Table IF EXISTS #tabTemp42;
go

CREATE TABLE #tabTemp41
(
   KeyInt41        int           not null,
   Name41          nvarchar(16)  not null,
   TargetDateTime  datetime      not null  default GetDate()
);

CREATE TABLE #tabTemp42
(
   KeyInt42 int          not null,   -- JOIN-able to KeyInt41.
   Name42   nvarchar(16) not null
);
go

INSERT into #tabTemp41 (KeyInt41, Name41) values (10, 't41-c');
INSERT into #tabTemp42 (KeyInt42, Name42) values (10, 't42-p');
go

CREATE PROCEDURE prc_gm29
AS
begin
SELECT * from #tabTemp41;
SELECT * from #tabTemp42;

SELECT t41.KeyInt41, t41.TargetDateTime, t41.Name41, t42.Name42
   from
                 #tabTemp41 as t41
      INNER JOIN #tabTemp42 as t42 on t42.KeyInt42 = t41.KeyInt41
end;
go

SET DATEFORMAT mdy;

SET FMTONLY ON;
EXECUTE prc_gm29;   -- Returns multiple tables.
SET FMTONLY OFF;
go
DROP PROCEDURE IF EXISTS prc_gm29;

DROP Table IF EXISTS #tabTemp41;
DROP Table IF EXISTS #tabTemp42;
go

/****  Actual Output:
[C:\JunkM\]
>> osql.exe -S myazuresqldb.database.windows.net -U somebody -P secret -d MyDatabase -i C:\JunkM\Issue-2246-a.SQL 

 KeyInt41    Name41           TargetDateTime
 ----------- ---------------- -----------------------

 KeyInt42    Name42
 ----------- ----------------

 KeyInt41    TargetDateTime          Name41           Name42
 ----------- ----------------------- ---------------- ----------------


[C:\JunkM\]
>>
****/

另请参阅See Also

SET 语句 (Transact-SQL)SET Statements (Transact-SQL)