整理 SQL Server 日常運維、疑難排查、效能調優高頻使用的系統預存程序與查詢語句,按功能分類,可直接複製執行或納入自動化巡檢腳本。
-- 完整版本資訊
SELECT @@VERSION;
-- 作業系統層級資訊
EXEC master..xp_msver;
-- 執行個體啟動參數
EXEC sp_configure;
-- 啟動時間
SELECT CONVERT(VARCHAR(30), login_time, 120)
FROM master..sysprocesses WHERE spid = 1;
-- 伺服器/實例名稱
PRINT 'Server Name: ' + CONVERT(VARCHAR(30), @@SERVERNAME);
PRINT 'Instance: ' + CONVERT(VARCHAR(30), @@SERVICENAME);
-- 所有資料庫名稱與大小
EXEC sp_helpdb;
-- 重命名資料庫 (需單一使用者模式)
EXEC sp_renamedb 'old_db', 'new_db';
-- 所有登入帳號資訊
EXEC sp_helplogins;
-- 伺服器角色成員
EXEC sp_helpsrvrolemember;
-- 修復遷移後孤立使用者
EXEC sp_change_users_login 'Auto_Fix', 'username';
-- 變更物件擁有者
EXEC sp_changeobjectowner 'schema.object', 'new_owner';
-- 備份登入帳號 (含 SID/密碼雜湊) → 遷移腳本
-- 參考 sp_help_revlogin (Microsoft 官方腳本)
-- 連結伺服器登入對應
EXEC sp_helplinkedsrvlogin;
-- 遠端登入資訊
EXEC sp_helpremotelogin;
-- 指定物件空間使用 (資料/索引/未使用)
EXEC sp_spaceused @objname = 'dbo.BigTable';
-- Top N 最大資料表 (預設 50)
EXEC sp_toptables;
-- 索引資訊
EXEC sp_helpindex @objname = 'dbo.BigTable';
-- 更詳細
EXEC sp_helpindex @objname = 'dbo.BigTable'; -- 2005+ 可用 sys.dm_db_index_physical_stats
-- 約束資訊
EXEC sp_helpconstraint @objname = 'dbo.BigTable';
-- 目前資料庫所有預存程序/函數
EXEC sp_stored_procedures;
-- 檢視程式碼
EXEC sp_helptext 'dbo.usp_MyProc';
-- 搜尋包含特定字串的物件
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE TEXT LIKE '%search_string%';
-- 建立加密程序
CREATE PROC dbo.usp_Secret WITH ENCRYPTION AS SELECT 1;
-- 解密 (需第三方工具或 DAC 連線)
-- 單次執行獲取核心健康指標
SELECT
@@SERVERNAME AS Server,
(SELECT COUNT(*) FROM sys.databases WHERE state_desc = 'ONLINE') AS OnlineDBs,
(SELECT SUM(size*8/1024) FROM master.sys.master_files) AS TotalSizeMB,
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio') AS BCHR,
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy') AS PLE_sec;
建議將上述指令整理為個人片段庫或 `snippets.json`,配合 `dbatools` (PowerShell) 或 `sqlcmd` 批次執行,打造專屬的自動化巡檢體系。