MSSQL, 取得TABLE名稱

下面SQL會因為p.value不知道型態, 而發生錯誤。

SELECT t.name AS TableName, p.value AS TableDesc
FROM sys.tables t
LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id AND p.minor_id = 0 AND p.class = 1
where (p.value is not null and p.value <> '')
  and upper(p.value) like '%AAA%'

因為 upper() 的傳入參數必須為字串, 所以可以把欄位型態強制轉為varchar, 解決此問題 (前題是確定p.value一定為字串)

SELECT t.name AS TableName, p.value AS TableDesc
FROM sys.tables t
LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id AND p.minor_id = 0 AND p.class = 1
where (p.value is not null and p.value <> '')
  AND UPPER(CAST(p.value AS NVARCHAR(MAX))) LIKE '%' + UPPER('AAA') + '%'

就可以解決問題了。