積沙成塔

【情境說明】

在 SQL Server 2012 以前的版本，若您想要取得查詢的中繼資料（Metadata），您可以使用 SET FMTONLY 選項，例如下列的程式碼，當 SET FMTONLY 設定為 ON 時，SELECT 敘述不會回傳任何查詢結果，而只會回傳中繼資料。

use Northwind

go

set fmtonly on
go
--資料表
select * from Region
--檢視
select * from Invoices
--預存程序
exec CustOrderHist 'MAISD'
set fmtonly off
go

執行結果：

您可以看到透過 SET FMTONLY ON 的方式取得中繼資料並不會有資料行的描述，在一些像是自訂報表設計工具，或是自動化產生文件的需求上，顯然無法達到我們所要的目的，而且在新版的 SQL Server 有更好的做法來幫助您取得中繼資料。

【建議作法】

利用 SET FMTONLY 取得查詢的中繼資料，在 SQL Server 2012 已經由 sp_describe_first_result_set / sys.dm_exec_describe_first_result_set 、sp_describe_undeclared_parameters 及 sys.dm_exec_describe_first_result_set_for_object 取代，建議您盡量使用新的語法，以免新版的 SQL Server 不支援時，您必須改寫您的指令碼。

您可以利用 sp_describe_first_result_set（或 sys.dm_exec_describe_first_result_set 動態管理檢視，兩者結果相同） 系統預存程序來取得查詢執行結果的中繼資料，語法如下：

sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch' 

    [ , [ @params = ] N'parameters' ] 

    [ , [ @browse_information_mode = ] <tinyint> ] ]

該語法接受三個引數，其中@tsql 參數是必要引數，用來傳入您想要取得中繼資料的查詢；@params 引數用來傳入 @tsql 引數中所使用的參數；@browse_information_mode 引數則是用來指定是否回傳索引鍵資料行和來源資料表的相關資訊，預設為 0 不回傳索引鍵資料行和來源資料表相關資訊。

下列的指令碼第 1  至 3 列，用來取得 @tsql 引數所執行查詢的中繼資料，而第 4 列指令碼由於 @browse_information_mode 設定為 1，因此會回傳索引鍵值資料行及來源資料表的中繼資料。而第 5 列指令碼，會根據 @browse_information_mode 設定為 2，來分析查詢並回傳檢視的名稱來做為來源資料行資訊。有關結果集每個資料行代表的意義在 MSDN 上有完整的說明，筆者就不在此贅述。

   1:  exec sys.sp_describe_first_result_set N'select ProductID from Invoices'

   2:  exec sys.sp_describe_first_result_set N'select ProductID from Invoices ',N'@RID int'

   3:  exec sys.sp_describe_first_result_set N'select ProductID from Invoices ',null,0

   4:  exec sys.sp_describe_first_result_set N'select ProductID from Invoices ',null,1

   5:  exec sys.sp_describe_first_result_set N'select ProductID from Invoices ',null,2

執行結果：

順帶一提的是，sp_describe_first_result_set 顧名思義只會回傳 @tsql 引數中的第一個查詢結果，因此若您執行下列的指令碼，將會回傳 Invoice 的中繼資料，而忽略 Region 的查詢結果，如下圖所示：

若您想要取得查詢參數的中繼資料，可以使用 sp_describe_undeclared_parameters 系統預存程序，其語法如下：

sp_describe_undeclared_parameters 

    [ @tsql = ] 'Transact-SQL_batch' 

    [ , [ @params = ] N'parameters' data type ] [, ...n]

其引數和 sp_describe_first_result_set 使用方式相同，但回傳的中繼資料卻不同，sp_describe_undeclared_parameters 只會回傳沒有在  @tsql 引數所定義的參數中繼資料，例如下列的指令碼，第 1 列由於已經有自行宣告 @tsql 引數中所使用的參數，因此並不會回傳任何查詢參數的中繼資料，而第 2 列筆者故意遺漏 @DDate 參數，因此您可以看到回傳 @DDate 的中繼資料（如下圖）。

exec sys.sp_describe_undeclared_parameters N'select * from region where RegionID = @RID',N'@RID int'

exec sys.sp_describe_undeclared_parameters N'select * from orders where OrderDate between @SDate and @DDate',N'@SDate datetime'

執行結果：

最後筆者介紹 sys.dm_exec_describe_first_result_set_for_object 動態檢視，可以用來取得預存程序或 Trigger 的中繼資料，其語法如下：

sys.dm_exec_describe_first_result_set_for_object 

    ( @object_id , @include_browse_information )

其中 @object_id 引數您必須透過 OBJECT_ID 函數來取得預存程序或 Trigger 的物件 ID 來傳入，而 @include_browse_information 則和上述使用方式相同。

下列指令碼用來回傳 Northwind 資料庫中的 CustOrderHist 預存程序的中繼資料：

select * from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('CustOrderHist'),0)

select * from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('CustOrderHist'),1)

select * from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('CustOrderHist'),2)

執行結果如下：

【延伸閱讀】

- 如何利用 T-SQL 查看 SQL Server 物件的定義

【參考資料】

- SET FMTONLY (Transact-SQL)

- sp_describe_first_result_set (Transact-SQL)

- sys.dm_exec_describe_first_result_set (Transact-SQL)

- sp_describe_undeclared_parameters (Transact-SQL)

- sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)