萬盛學電腦網

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

SQL Server DBA 必備腳本大全:結構描述、索引、約束、分頁、字串處理、日期運算

腳本分類目錄

  1. 查看表/欄位擴充屬性(MS_Description)
  2. 欄位增刪改、重命名
  3. 擴充屬性增刪改
  4. 欄位 NULL/NOT NULL 切換
  5. 主鍵、外鍵、索引建立與修改
  6. 預設值約束增刪改
  7. 字串分割函數
  8. FOR XML AUTO / PATH 多種用法
  9. STUFF/REPLACE 字串操作
  10. 多表 UPDATE 聯結寫法
  11. 日期時間函數大全
  12. 行轉列/列轉行兩種實現
  13. 約束腳本化管理
  14. CTE (WITH) 遞歸查詢
  15. UPDATE 防呆模式 (@@ROWCOUNT/@@ERROR)
  16. 大表分批刪除腳本

精選高頻腳本示範

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 錯誤處理。
  • 生產環境變更前,必須在測試環境驗證並備份。
copyright © 萬盛學電腦網 all rights reserved