一、數據查詢與處理
-- 1. 按中文筆畫排序
SELECT * FROM T ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;
-- 2. 密碼加密與比較
SELECT PWDENCRYPT('pwd'); -- 加密
SELECT PWDCOMPARE('pwd', 0x...); -- 比較,1=匹配
-- 3. 動態獲取表所有列名
DECLARE @Cols VARCHAR(MAX) = '';
SELECT @Cols += ',' + QUOTENAME(C.NAME)
FROM SYS.COLUMNS C JOIN SYS.OBJECTS O ON C.OBJECT_ID=O.OBJECT_ID
WHERE O.NAME='TableA';
EXEC('SELECT ' + STUFF(@Cols,1,1,'') + ' FROM TableA');
二、系統信息掌控
-- 4. 查看磁盤剩餘空間
EXEC MASTER..XP_FIXEDDRIVES;
-- 5. 快速比較兩表數據是否相同
IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM A) =
(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM B)
PRINT '相等'; ELSE PRINT '不相等';
-- 6. 幹掉所有 Profiler 進程
DECLARE Cur CURSOR FOR SELECT 'KILL '+SPID FROM MASTER..SYSPROCESSES
WHERE PROGRAM_NAME IN ('SQL Profiler','SQL 事件探查器');
OPEN Cur; FETCH NEXT FROM Cur INTO @Cmd;
WHILE @@FETCH_STATUS=0 BEGIN EXEC(@Cmd); FETCH NEXT FROM Cur INTO @Cmd; END
CLOSE Cur; DEALLOCATE Cur;
三、分頁與排序模板
-- 7. 顯式分頁(需主鍵 ID)
-- 前 N 行
SELECT TOP N * FROM T;
-- 第 N 到 M 行
SELECT TOP (M-N) * FROM T WHERE ID IN (SELECT TOP M ID FROM T) ORDER BY ID DESC;
-- 最後 N 行
SELECT TOP N * FROM T ORDER BY ID DESC;
四、對象管理與元數據
-- 8. 重命名數據庫
EXEC SP_RENAMEDB 'OldName', 'NewName';
-- 9. 獲取所有用戶表
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' AND STATUS>=0;
-- 10. 獲取表所有列
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TableName');
-- 11. 尋找引用某表的視圖/存儲過程/函數
SELECT A.* FROM SYSOBJECTS A, SYSCOMMENTS B
WHERE A.ID=B.ID AND B.TEXT LIKE '%TableName%';
-- 12. 列出所有存儲過程
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='P';
-- 13. 查詢非 SA 創建的用戶數據庫
SELECT DBID, NAME FROM MASTER..SYSDATABASES WHERE SID <> 0x01;
-- 14. 獲取表結構(列名+類型)
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableName';