SQL Server 2012 的 T-SQL 新功能–Metadata 探索

本文將介紹 SQL Server 2012 新的中繼資料探索的 T-SQL 語法。

情境說明

在 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

執行結果:

image

您可以看到透過 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

執行結果:

image

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

image

若您想要取得查詢參數的中繼資料,可以使用 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'

執行結果:

image

最後筆者介紹 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)

執行結果如下:

image

延伸閱讀

- 如何利用 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)