Hao's learning log

PIVOT使用   --【簡易版】 SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( -- 設定彙總欄位及方式 MAX(ShowText) -- 設定轉置欄位，並指定轉置欄位中需彙總的條件值作為新欄位 FOR LangType IN ([zh-TW], [zh-CN], [en-US]) ) p; --【動態生成欄位】 -- 使用 FOR XML PATH 動態生成轉置列清單 DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX); SELECT @cols = STUFF(( SELECT ',' + QUOTENAME(LangType) FROM ( SELECT DISTINCT LangType FROM dbo.Lang ) AS Sub ORDER BY CASE WHEN LangType = 'zh-TW' THEN 1 WHEN LangType = 'zh-CN' THEN 2 ELSE 3 END, LangType FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- 動態 PIVOT 查詢 SET @query = ' SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( MAX(ShowText) FOR LangType IN (' + @cols + ') ) p; '; -- 執行動態 SQL EXEC sp_executesql @query; 參考網址:  https://dotb...

sp_send_dbmail使用   EXEC msdb.dbo.sp_send_dbmail @profile_name='MES_MAIL', @recipients='eMail Address', @blind_copy_recipients='eMail Address', @subject = '主旨', @body = '內容', @body_format = 'HTML'; SELECT * FROM msdb.dbo.sysmail_allitems --查詢寄送的郵件記錄 參考網址:  https://vito-note.blogspot.com/2013/06/database-mail.html https://ithelp.ithome.com.tw/articles/10198042?sc=pt https://learn.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver16 https://blog.csdn.net/Laaguid/article/details/89671396 By Hao★

查詢StoredProcedure中的Key Word   SELECT name AS StoredProcedureName, OBJECT_DEFINITION(OBJECT_ID(name)) AS ProcedureDefinition FROM sys.procedures WHERE upper(OBJECT_DEFINITION(OBJECT_ID(name))) LIKE '%KEY_WORD%' ORDER BY name; 參考網址:  By Hao★