1. 中文筆畫排序
SELECT * FROM Customers ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;
2. 密碼加密與比較（僅演示，生產用 PBKDF2/BCrypt/Argon2）
SELECT PWDENCRYPT('PlainPwd'); -- 加密
SELECT PWDCOMPARE('PlainPwd', 0xHash); -- 比較，返回 1/0
3. 獲取表所有列名（動態拼接）
DECLARE @Cols NVARCHAR(MAX) = '', @Sql NVARCHAR(MAX);
SELECT @Cols += QUOTENAME(name) + ',' FROM sys.columns WHERE object_id = OBJECT_ID('TableA');
SET @Cols = LEFT(@Cols, LEN(@Cols)-1);
SET @Sql = 'SELECT ' + @Cols + ' FROM TableA';
EXEC sp_executesql @Sql;
4. 查看磁盤分區空間
EXEC master..xp_fixeddrives; -- 返回 Drive, MB_Free
5. 比較兩表數據是否完全相同
IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA) =
(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB)
PRINT '相同';
ELSE PRINT '不同';
6. 殺掉所有 Profiler/事件探查器進程
DECLARE @KillCmd NVARCHAR(MAX) = '';
SELECT @KillCmd += 'KILL ' + CAST(spid AS VARCHAR) + ';'
FROM sys.sysprocesses
WHERE program_name IN ('SQL Profiler', 'SQL 事件探查器');
EXEC sp_executesql @KillCmd;
7. 記錄集分頁取數（三種模式）
-- 前 N 條
SELECT TOP 10 * FROM TableA;
-- 第 N 到 M 條 (需主鍵 ID)
SELECT TOP 10 * FROM TableA WHERE ID IN (SELECT TOP 20 ID FROM TableA) ORDER BY ID DESC;
-- 最後 N 條
SELECT TOP 10 * FROM TableA ORDER BY ID DESC;
8. 修改數據庫名稱
ALTER DATABASE OldName MODIFY NAME = NewName; -- 2005+ 推薦
-- 或舊版
EXEC sp_renamedb 'OldName', 'NewName';
9. 獲取所有用戶表
SELECT name FROM sys.objects WHERE type = 'U' AND is_ms_shipped = 0;
10. 獲取表所有列
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableA');
11. 查找引用某表的對象（視圖/過程/函數/觸發器）
SELECT o.* FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE m.definition LIKE '%TableA%';
12. 所有存儲過程列表
SELECT name FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;
13. 用戶創建的數據庫（排除系統庫）
SELECT name FROM sys.databases WHERE owner_sid <> 0x01; -- sa 的 SID 為 0x01
-- 或
SELECT dbid, name FROM master..sysdatabases WHERE sid <> 0x01;
14. 表結構詳情（列名、類型、長度、精度、可空、標識）
SELECT
c.name AS ColumnName,
t.name AS DataType,
c.max_length,
c.precision,
c.scale,
c.is_nullable,
c.is_identity
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('TableA');
