[MSSQL2016] FORMATMESSAGE格式化訊息

MSSQL於2016版本中提供FORMATMESSAGE函數，在此僅針對訊息格式化處理進行探討，語法如下。

FORMATMESSAGE('msg_string' , [ param_value [ ,...n ] ] )

msg_string

為格式化訊息字串，最多可支援2,047字元，包含參數值(s)預留位置，超過部分將被截斷。

param_value

為訊息字串中所用之參數值，依位置依次指定，最多支援20個參數值。

FORMATMESSAGE所採用之參數用法，類似C/C++輸出格式簡化版本，但僅支援字串及整數兩大類(無浮點數)，以下相關範例及說明，以常用字串(%s)及10進制具符號整數(%d)為例。

參數	說明
%s	字串
%d	10進制具符號(正負號)整數
%u	10進制無符號整數
%o	8進制無符號整數
%x	16進制無符號整數

FORMATMESSAGE基本用法是將訊息字串依序置換參數值方式組合輸出字串，另外尚可搭配其他額外參數（類似C/C++）以長度限定、字串填補及方向(左或右側)等擴展字串處理能力。

填補功能-長度限定

可於"%"和參數間額外增加數字以控制其輸出長度，如%5s代表當輸出長度小於5位之字串時，將於左方以空白字進行補足5碼，尚可控制輸出之最大長度（強制截斷），應用及對應參數整理如下表。

#	長度參數 (%與字母間)	意義	說明
1	整數   (ex: %5s) (字串/數字均支援)	最小長度(m)	如長度<m時，以空白填補至最小長度(m)
2	浮點數 (ex: %5.8s) (不支援數字)	最小長度.最大長度(m.n)	如長度<m時，以空白填補至最小長度(m) 如長度>n時，則超出最大長度(n)部分將截斷

當長度不足時，預設以空白字元進行填補(左方)，也可改用"0"進行填補，則需於長度限定參數前額外增加一個"0"即可，如%05d代表當輸出小於5位之數字時，將於前方(左側)補0，使其總長度度為5位。

填補功能-填補/對齊方向

另外，可控制輸出以左側(右方填補)或右側(左方填補，預設)對齊，如欲變更為右方填補時，則在"%"和字母間額外加入負號(-)即可。如%-8d，代表輸出長度8整數置左對齊。

類型	SQL	[輸出]
數字	SELECT Val , FORMATMESSAGE('Val: %d', Val) "Val: %d"  , FORMATMESSAGE('[%5d]',   Val) "[%5d]"  , FORMATMESSAGE('[%05d]',  Val) "[%05d]"  , FORMATMESSAGE('[%-05d]', Val) "[%-05d]"  , FORMATMESSAGE('[%5.5d]', Val) "[%5.5d]" FROM (VALUES (123) , (12345) , (123456)) AS Test(Val)
字串	SELECT Val  , FORMATMESSAGE('Val: %s',  Val) "Val: %s"  , FORMATMESSAGE('[%5s]',    Val) "[%5s]"  , FORMATMESSAGE('[%05s]',   Val) "[%05s]"  , FORMATMESSAGE('[%-05s]',  Val) "[%-05s]"  , FORMATMESSAGE('[%5.5s]',  Val) "[%5.5s]" FROM (VALUES ('ABC') , ('ABCDE') , ('ABCDEF')) AS Test(Val)

範例中各欄位說明如下:

範例	字串	數字	說明
直接輸出	%s	%d	直接置換輸出
右補空白至長度5	%5s	%5d	置左，右補空白至長度5，如超過仍完整輸出
左補空白至長度5	%-5s	%-5d	置右，左補空白至長度5，如超過仍完整輸出
右補空白至長度5(m) 如超過5(n)則取5(n)	%5.5s	%5.5d	置左，右補空白至長度5，如已超過5(n)，以%s則取5碼(n)其餘截斷，但%d仍將完整輸出。

FORMATMESSAGE格式化訊息(字串)函數大幅提升MSSQL字串輸出處理能力，簡化過去需用多函數組建而成之功能，如下範例。

SELECT *

  , REPLACE(FORMATMESSAGE('[%-5s%05d%-5.5s]', Name, Vote, Commets), ' ','　' ) OUTPUT

FROM (VALUES ('閃電麥坤', 1912, '洲際盃')

, ('拖線', 945, '住在油車水鎮')) AS iVoting(Name, Vote, Commets)

取得Table Schema

以下將利用系統表格取得Table Schema，如Owner(Schema)、Table Name、Column Name、Data Type、Nullable及欄位說明等幾項重要項目。

ORACLE相對簡單許多，僅需查詢

1.       ALL_TAB_COLUMNS (欄位相關屬性-名稱、型態等)

2.       ALL_COL_COMMENTS(欄位說明)

MSSQL則相對較為複雜，建議優先採用2005版本上開始提供的系統表格，如

1.       sys.tables (資料表)

2.       sys.columns (欄位)

3.       sys.types (資料型別)

4.       sys.extended_properties (延伸屬性-取得欄位說明)

	SQL
ORACLE	SELECT COL.OWNER      , COL.TABLE_NAME      , COL.COLUMN_ID      , COL.COLUMN_NAME      , COL.DATA_TYPE          || CASE WHEN COL.DATA_TYPE IN ('NUMBER', 'DECIMAL') THEN                               ' (' || COL.DATA_PRECISION || ', ' || COL.DATA_SCALE || ')'                     WHEN INSTR(COL.DATA_TYPE, 'CHAR')>0  THEN                             ' (' || COL.DATA_LENGTH || ') '             END DATA_TYPE      , DECODE(COL.NULLABLE, 'Y' ,'', COL.NULLABLE)  NULLABLE      , CMT.COMMENTS             FROM ALL_TAB_COLUMNS COL, ALL_COL_COMMENTS CMT WHERE 1=1        AND COL.COLUMN_NAME = CMT.COLUMN_NAME        AND COL.TABLE_NAME  = CMT.TABLE_NAME        AND COL.OWNER       = CMT.OWNER       AND COL.OWNER       = 'HR'
MSSQL	SELECT OBJECT_SCHEMA_NAME(tab.object_id)   Sch_Name , tab.name                                                                     Table_Name , col.column_id                                                             column_id , col.name                                                                     Column_name , typ.name 　  + CASE WHEN typ.name IN ('numeric','decimal') THEN               --(1).數值型態                                  CONCAT(' (', col.PRECISION, ', ', col.scale, ')')                       WHEN CHARINDEX('char', typ.name)>0  THEN          --(2).字元型態                                    ' ('                                    +　CASE WHEN col.max_length =-1 THEN 'max'  --, 長度-1為max                                    　　　            WHEN typ.name LIKE 'n%' THEN  CAST(col.max_length/2  AS varchar(10))                                                      ELSE CAST(col.max_length AS varchar(10))                                            END 　　　　　               + ')'                    ELSE ''                                                                                    --(3). 其他型態 　　END Data_Type , CASE WHEN col.is_nullable=0 THEN '' ELSE 'Y' END  Nullable  , Comments , tab.object_id --, col.max_length FROM sys.tables tab                -- 1. 資料表                 INNER JOIN sys.columns AS col  -- 2. 欄位                                 ON tab.object_id = col.object_id                 INNER JOIN sys.types AS typ    -- 3. 型態                                 ON col.user_type_id=typ.user_type_id                 OUTER APPLY               -- 4. 欄位說明                                 (                                 SELECT CAST(sep.value AS VARCHAR(1000)) Fld_Comments                                 FROM  sys.extended_properties sep                                 WHERE 1=1                                                 AND tab.object_id = sep.major_id                                                 AND col.column_id = sep.minor_id                                                 AND sep.name    　= 'MS_Description'                                 ) fc (Comments)

以MSSQL為例

(1)    建立資料表

CREATE TABLE CUST

(

Cust_Id              int IDENTITY(1, 1)        NOT NULL,  --未使用，暫先建立

ID_No               varchar(10)                                     NOT NULL,

Cust_Name       nvarchar(20)                                   NOT NULL,

Addr                  nvarchar(200)                                          NULL,

Phone                varchar(20)                                               NULL

CONSTRAINT PK_CUST PRIMARY KEY CLUSTERED (ID_No)

)

EXEC sp_addextendedproperty  N'MS_Description','客戶代碼', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Cust_Id'

EXEC sp_addextendedproperty  N'MS_Description','身份證號', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','ID_No'

EXEC sp_addextendedproperty  N'MS_Description','客戶姓名', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Cust_Name'

EXEC sp_addextendedproperty  N'MS_Description','地址', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Addr'

EXEC sp_addextendedproperty  N'MS_Description','電號號碼', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Phone'

(2)    產出結果