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

適用於: 是SQL Server 是Azure SQL Database 是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)