MSSQL 實戰技巧大全：從日期轉換到對象腳本生成

常用開發技巧速查

1. 日期與排序

-- 長日期轉短日期 (YYYY-MM-DD)
CONVERT(char(10), GETDATE(), 120)

-- 按姓氏筆畫排序
SELECT * FROM TableName ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_ci_as

2. 數據加密與比對

-- 加密
SELECT ENCRYPT('原始密碼')
SELECT PWDENCRYPT('原始密碼')

-- 驗證 (返回 1 為匹配)
SELECT PWDCOMPARE('原始密碼', '加密後密碼')

3. 動態獲取表結構

DECLARE @list VARCHAR(1000), @sql NVARCHAR(1000)
SELECT @list = @list + ',' + b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.name = '表A'
SET @sql = 'SELECT ' + RIGHT(@list, LEN(@list)-1) + ' FROM 表A'
EXEC (@sql)

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 hcforeach CURSOR GLOBAL FOR
SELECT 'KILL ' + RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN ('SQL Profiler', N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'

7. 分頁查詢模式

-- 前 N 條
SELECT TOP N * FROM 表

-- N 到 M 條 (需主鍵 ID)
SELECT TOP M-N * FROM 表
WHERE ID IN (SELECT TOP M ID FROM 表)
ORDER BY ID DESC

-- 最後 N 條
SELECT TOP N * FROM 表 ORDER BY ID DESC

8. 核心對象管理

-- 重命名數據庫
EXEC sp_renamedb 'old_name', 'new_name'

-- 所有用戶表
SELECT name FROM sysobjects WHERE xtype='u' AND status>=0

-- 表所有列
SELECT name FROM syscolumns WHERE id=OBJECT_ID('表名')

-- 相關視圖/存儲過程/函數
SELECT a.* FROM sysobjects a, syscomments b
WHERE a.id = b.id AND b.text LIKE '%表名%'

-- 所有存儲過程
SELECT name AS 存儲過程名稱 FROM sysobjects WHERE xtype='P'

-- 用戶創建的數據庫
SELECT * FROM master..sysdatabases
WHERE sid NOT IN (SELECT sid FROM master..syslogins WHERE name='sa')
-- 或
SELECT dbid, name AS DB_NAME FROM master..sysdatabases WHERE sid <> 0x01

-- 表結構詳情
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = '表名'

進階技巧：表結構複製與對象腳本生成

僅複製表結構

SELECT TOP 0 * INTO [t1] FROM [t2]

獲取任意對象創建腳本 (基於 SQL-DMO)

通過創建函數 fgetscript，利用 sp_OACreate 實例化 SQLDMO.SQLServer，連接服務器獲取數據庫集合，調用對象 Script 方法生成完整 DDL。支持 Windows 認證與 SQL 認證雙模式，包含完整錯誤處理與十六進制錯誤碼轉換。適用於自動化部署、版本控制、異構遷移等場景。

-- 使用示例
SELECT dbo.fgetscript('SERVER_NAME', 'sa', 'pwd', 'DB_NAME', 'OBJECT_NAME')