SQL Server 開發必備：14 條高頻實用程式碼片段速查手冊

前言：工欲善其事，必先利其器

資料庫開發與維運中，反覆出現的特定需求 (中文排序、加密、結構查詢、維護指令) 若能沉澱為標準化程式碼片段，將大幅提升效率並減少錯誤。本文精選 14 條經驗驗證的 T-SQL 實用腳本，涵蓋業務邏輯、安全、中繼資料查詢、效能診斷四大領域。

業務邏輯類

1. 中文筆畫排序

SELECT * FROM TableName
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;
-- CI_AS = 不區分大小寫、區分重音
-- 亦可用 Chinese_PRC_Stroke_CS_AS (區分大小寫)

2. 密碼加密與比對 (舊版相容，新版建議用 HASHBYTES)

-- 加密
SELECT ENCRYPT('原始密碼');           -- 簡單加密 (已棄用)
SELECT PWDENCRYPT('原始密碼');        -- SQL Server 登入密碼雜湊演算法

-- 驗證
SELECT PWDCOMPARE('原始密碼', '加密後密碼');  -- 回傳 1=相符, 0=不符

現代最佳實務： 使用 HASHBYTES('SHA2_256', @pwd + @salt) + 常數時間比對，勿用 PWDENCRYPT (演算法不透明、不可移植)。

3. 動態取得資料表所有欄位清單 (逗號分隔)

DECLARE @list VARCHAR(1000) = '', @sql NVARCHAR(1000);
SELECT @list = @list + ',' + QUOTENAME(c.name)
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = '表A';

SET @sql = 'SELECT ' + STUFF(@list, 1, 1, '') + ' FROM 表A';
EXEC sp_executesql @sql;

改良點： 使用 QUOTENAME 防止保留字/特殊字元錯誤，STUFF 移除前導逗號，sp_executesql 支援參數化。

系統診斷與維運類

4. 檢視磁碟分區可用空間

EXEC master..xp_fixeddrives;
-- 回傳：Drive | MB Free

5. 快速比較兩張表資料是否完全相同

IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM A) =
   (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM B)
    PRINT '資料相同';
ELSE
    PRINT '資料不同';
-- 注意：極微小機率發生校驗碼碰撞，嚴格場景建議逐欄比對或 EXCEPT/INTERSECT

6. 批次終結所有 SQL Profiler / 事件探查器連線

DECLARE hcforeach CURSOR GLOBAL FOR
SELECT 'KILL ' + RTRIM(spid)
FROM master.dbo.sysprocesses
WHERE program_name IN ('SQL Profiler', N'SQL 事件探查器');

OPEN hcforeach;
FETCH NEXT FROM hcforeach INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@cmd);
    FETCH NEXT FROM hcforeach INTO @cmd;
END
CLOSE hcforeach; DEALLOCATE hcforeach;
-- 或簡化：EXEC sp_msforeach_worker 'KILL ?' (需確認相容性)

7. 分頁查詢三大模式 (需有主鍵/唯一鍵 ID)

-- 第一頁：Top N
SELECT TOP (N) * FROM 表 ORDER BY ID;

-- 第 N 到 M 筆 (KeySet 分頁，效能佳)
SELECT TOP (M-N) * FROM 表
WHERE ID IN (SELECT TOP (M) ID FROM 表 ORDER BY ID)
ORDER BY ID DESC;

-- 最後 N 筆
SELECT TOP (N) * FROM 表 ORDER BY ID DESC;

現代標準 (SQL Server 2012+)： ORDER BY ID OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY。

8. 修改資料庫名稱

-- 單一使用者模式下執行
ALTER DATABASE old_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_renamedb 'old_name', 'new_name';
ALTER DATABASE new_name SET MULTI_USER;

中繼資料查詢類

9. 取得目前資料庫所有使用者資料表

SELECT name FROM sys.objects
WHERE type = 'U' AND is_ms_shipped = 0;
-- sysobjects 為舊版相容視圖，建議用 sys.objects

10. 取得指定資料表所有欄位

SELECT name FROM sys.columns
WHERE object_id = OBJECT_ID('表名');

11. 查詢參考特定資料表的 View / SP / Function

SELECT DISTINCT o.name, o.type_desc
FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE m.definition LIKE '%表名%'
  AND o.type IN ('V','P','FN','IF','TF');

12. 列出目前資料庫所有預存程序

SELECT name AS [預存程序名稱]
FROM sys.objects
WHERE type = 'P' AND is_ms_shipped = 0;

13. 查詢非 SA 建立的使用者資料庫

SELECT database_id, name AS DB_NAME
FROM sys.databases
WHERE owner_sid <> 0x01;  -- 0x01 為 SA 的 SID

14. 查詢資料表欄位與資料類型 (標準資訊綱要)

SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale, is_nullable
FROM information_schema.columns
WHERE table_name = '表名';

使用建議與版本相容性備註

• 優先使用 sys.* 目錄檢視與 information_schema，避免直接查詢舊版系統表 (sysobjects, syscolumns)。
• 動態 SQL 必須使用 sp_executesql + 參數化，防止 SQL 注入並利用計畫快取。
• 加密相關功能請依資安政策選用 HASHBYTES、ENCRYPTBYPASSPHRASE、Always Encrypted 等現代 API。
• 大量資料操作 (分頁、刪除、更新) 應分批次執行，避免長交易封鎖與 Log 膨脹。