萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 開發必備:30 條高頻實用腳本速查手冊

一、數據查詢與處理

-- 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';
copyright © 萬盛學電腦網 all rights reserved