Hao's learning log

   將資料表索引進行重建(REBUILD & REORGANIZE)   DECLARE @dbName NVARCHAR(128) SET @dbName = DB_NAME() DECLARE @schemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @indexName NVARCHAR(128) DECLARE @indexId INT DECLARE @fragPercent FLOAT DECLARE @sql NVARCHAR(MAX) -- 迴圈檢查每個索引的碎片程度 DECLARE indexCursor CURSOR FOR SELECT s.name AS schemaName, t.name AS tableName, i.name AS indexName, i.index_id AS indexId, ips.avg_fragmentation_in_percent AS fragPercent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE ips.avg_fragmentation_in_percent > 10 -- 篩選需要維護的索引 AND i.type IN (1, 2) -- 只處理 Clustered 和 Non-Clustered 索引 AND i.is_disabled = 0 -- 忽略已停用的索引 ORDER BY tableName OPEN indexCursor FETCH NEXT FROM indexCursor INTO @schemaName, @tableName...

  ZebraDesigner 擴充字體步驟 安裝 Zebra Setup Utilities 安裝過程皆下一步即可 下載印表機字體 字體下載完後，編輯器上面就能選到新的字體， 且使用列印至檔案所產出的.prn就不會亂碼。 參考網址: https://blog.csdn.net/lixiaojie15523694746/article/details/103991751 By Hao★

  查詢所有Procedure   --MS SQL使用 SELECT name AS ProcedureName, create_date AS 建立時間, modify_date AS 異動時間 FROM sys.procedures WHERE is_ms_shipped = 0 -- 只顯示使用者自訂的預存程序 ORDER BY ProcedureName; --通用版本(尚未測試) SELECT ROUTINE_TYPE, ROUTINE_SCHEMA AS SchemaName, ROUTINE_NAME AS ProcedureName, CREATED AS 建立時間, LAST_ALTERED AS 最後異動時間 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' -- 只顯示預存程序 ORDER BY ROUTINE_NAME; 參考網址:  By Hao★

  Procedure最後執行時間   SELECT OBJECT_NAME(object_id) AS ProcedureName, last_execution_time AS 最後執行時間 FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id) <> '' ORDER BY ProcedureName; 參考網址:  By Hao★

查詢資料表使用紀錄   SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update AS 最後更新時間, last_user_seek AS 最後搜尋時間, last_user_scan AS 最後掃描時間 FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()           -- 當前數據庫 AND OBJECT_ID = OBJECT_ID('TableName') -- 指定資料表名稱 ORDER BY TableName; 參考網址:  By Hao★

UI介面多國語系切換功能   Imports System.ComponentModel Imports System.Globalization Imports System.Resources 'UI介面語系轉換 Public Class C_UILanguage Private systemResourceManager As ResourceManager Private errorResourceManager As ResourceManager Dim CRM As System.ComponentModel.ComponentResourceManager Public Sub New() systemResourceManager = New ResourceManager("專案名稱.Resources", GetType(C_UILanguage).Assembly) errorResourceManager = New ResourceManager("專案名稱.Resources.ErrorMessages", GetType(C_UILanguage).Assembly) End Sub Public Sub _Main(ByVal _Form As Windows.Forms.Form) '檢查引數 If _Form Is Nothing Then _Form.Text = "Form" Throw New ArgumentNullException(_Form.Text) End If CRM = Nothing '切換介面顯示的語言 Try CRM = New ComponentResourceManager(_Form.GetType) ' 設置表單的標題 CRM.ApplyResources(_Form, "$this...

匯出 DataGridView 資料到 Excel   Imports System.Runtime.InteropServices Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click ' 檢查DataGridView是否有數據 If dgv1.Rows.Count = 0 Then MessageBox.Show("沒有資料可以匯出", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If ' 檢查是否安裝Excel If Not IsExcelInstalled() Then MessageBox.Show("這台電腦沒有安裝 Office Excel，無法繼續執行！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If Try ' 開始匯出資料到 Excel ExportDataGridViewToExcel(dgv1) Catch ex As Exception MessageBox.Show(ex.ToString, "匯出 Excel 發生錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub ' 檢查是否安裝Excel Private Function IsExcelInstalled() As Boolean Try Dim excelApp As New Excel.Application() If excelApp IsNot Nothing Then excelApp.Quit() Return True ...

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★