腳本分類目錄
- 查看表/欄位擴充屬性(MS_Description)
- 欄位增刪改、重命名
- 擴充屬性增刪改
- 欄位 NULL/NOT NULL 切換
- 主鍵、外鍵、索引建立與修改
- 預設值約束增刪改
- 字串分割函數
- FOR XML AUTO / PATH 多種用法
- STUFF/REPLACE 字串操作
- 多表 UPDATE 聯結寫法
- 日期時間函數大全
- 行轉列/列轉行兩種實現
- 約束腳本化管理
- CTE (WITH) 遞歸查詢
- UPDATE 防呆模式 (@@ROWCOUNT/@@ERROR)
- 大表分批刪除腳本
精選高頻腳本示範
1. 檢視/新增/修改欄位描述
-- 檢視
SELECT * FROM fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'TableName', 'column', NULL);
-- 新增欄位描述
EXEC sp_addextendedproperty
@name = N'MS_Description', @value = N'欄位說明',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'TableName',
@level2type = N'COLUMN', @level2name = N'ColumnName';
-- 修改描述
EXEC sp_updateextendedproperty
@name = N'MS_Description', @value = N'新說明',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'TableName',
@level2type = N'COLUMN', @level2name = N'ColumnName';
2. 安全切換欄位 NULL 屬性
-- 允許 NULL
ALTER TABLE TableName ALTER COLUMN ColumnName INT NULL;
-- 禁止 NULL(需先確保無 NULL 值)
UPDATE TableName SET ColumnName = 0 WHERE ColumnName IS NULL;
ALTER TABLE TableName ALTER COLUMN ColumnName INT NOT NULL;
3. 建立主鍵/索引(檢查存在再建)
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'PK_TableName' AND object_id = OBJECT_ID('TableName'))
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY (Id);
4. 字串分割函數(表值函數,高效能)
CREATE FUNCTION dbo.SplitString (@str NVARCHAR(MAX), @delim NCHAR(1))
RETURNS @t TABLE (val NVARCHAR(4000))
AS BEGIN
DECLARE @xml XML = N'';
INSERT INTO @t(val) SELECT T.c.value('.', 'NVARCHAR(4000)') FROM @xml.nodes('r') AS T(c);
RETURN;
END;
5. FOR XML PATH 實現行轉字串(如 ID 列表)
SELECT STUFF((
SELECT ',' + CAST(Id AS VARCHAR(20))
FROM TableName
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS IdList;
6. 多表 UPDATE 聯結寫法
UPDATE t1
SET t1.ColA = t2.ColB
FROM Table1 t1
JOIN Table2 t2 ON t1.Id = t2.Id
WHERE t1.Status = 1;
7. 日期常用運算
-- 本月第一天
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
-- 本月最後一天
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0));
-- 年齡計算
SELECT DATEDIFF(YEAR, BirthDate, GETDATE()) -
CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, BirthDate, GETDATE()), BirthDate) > GETDATE() THEN 1 ELSE 0 END;
8. 大表分批刪除(避免記錄檔暴增、鎖升級)
DECLARE @rows INT = 1, @batchSize INT = 5000;
WHILE @rows > 0
BEGIN
DELETE TOP (@batchSize) FROM BigTable WHERE CreateDate < '2020-01-01';
SET @rows = @@ROWCOUNT;
WAITFOR DELAY '00:00:01'; -- 釋放鎖、緩衝記錄檔
END
使用建議
- 將常用腳本存入 範本總管 或 Snippets,提升效率。
- 所有 DDL 操作務必在交易中執行,並包含 TRY...CATCH 錯誤處理。
- 生產環境變更前,必須在測試環境驗證並備份。