SQL 實用腳本大全：元數據查詢、動態 SQL、分頁、行列轉換、大量刪除、跨伺服器操作等

目錄導航

1. 元數據查詢
2. DDL 操作 (加/修/刪欄位、描述、主鍵、索引、預設值)
3. 字串分割函數
4. FOR XML AUTO / PATH 進階用法
5. STUFF 字串插入/替換
6. 多表 UPDATE
7. 日期函數常用模式
8. 行列轉換 (變量法 / FOR XML PATH)
9. 約束查詢/刪除/建立
10. WITH CTE (臨時表/遞迴)
11. UPDATE 錯誤/列數檢查模式
12. 大量資料分批刪除腳本
13. 跨伺服器查詢/匯入/更新 (Linked Server / OPENROWSET / OPENDATASOURCE)

1. 元數據查詢

-- 檢視表/欄位擴展屬性 (描述)
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'TABLE_NAME', 'column', NULL);

-- 所有使用者表
SELECT name FROM sys.objects WHERE type = 'U';

-- 表欄位清單
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE_NAME');

2. DDL 常用模式

-- 加欄位 (可多欄)
ALTER TABLE t ADD col1 INT, col2 NVARCHAR(50);

-- 修改欄位型別
ALTER TABLE t ALTER COLUMN col1 BIGINT;

-- 刪欄位
ALTER TABLE t DROP COLUMN col1;

-- 重命名欄位
EXEC sp_rename 't.col1', 'col2', 'COLUMN';

-- 加入/修改/刪除欄位描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'說明', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't', @level2type=N'COLUMN',@level2name=N'col1';
EXEC sp_updateextendedproperty 'MS_Description', N'新說明', 'user', 'dbo', 'table', 't', 'column', 'col1';
EXEC sp_dropextendedproperty 'MS_Description', 'user', 'dbo', 'table', 't', 'column', 'col1';

-- 建立主鍵 (含複合)
ALTER TABLE t ADD CONSTRAINT PK_t PRIMARY KEY (col1, col2);

-- 建立索引 (叢集/非叢集)
CREATE CLUSTERED INDEX IX_t_col ON t(col);
CREATE NONCLUSTERED INDEX IX_t_col ON t(col);

-- 預設值約束
ALTER TABLE t ADD CONSTRAINT DF_t_col DEFAULT (0) FOR col;
ALTER TABLE t DROP CONSTRAINT DF_t_col; -- 刪除後重建以修改

3. 字串分割函數 (SQL 2016 前)

CREATE FUNCTION dbo.Split(@str NVARCHAR(MAX), @sep NCHAR(1))
RETURNS @t TABLE (val NVARCHAR(4000))
AS BEGIN
    DECLARE @pos INT = CHARINDEX(@sep, @str);
    WHILE @pos > 0 BEGIN
        INSERT @t VALUES(SUBSTRING(@str, 1, @pos-1));
        SET @str = SUBSTRING(@str, @pos+1, LEN(@str));
        SET @pos = CHARINDEX(@sep, @str);
    END
    IF @str <> '' INSERT @t VALUES(@str);
    RETURN;
END;
-- 用法: SELECT * FROM dbo.Split('a,b,c', ',');

4. FOR XML 進階

DECLARE @t TABLE (id INT, name NVARCHAR(50));
INSERT @t VALUES (1,'A'),(2,'B');

-- AUTO: 表名為元素
SELECT * FROM @t FOR XML AUTO;
-- 

-- PATH: 自訂元素名
SELECT * FROM @t FOR XML PATH('Item'), ROOT('Root');
-- 

-- PATH('') + STUFF: 串聯字串 (高效行轉列)
SELECT STUFF((SELECT ',' + name FROM @t FOR XML PATH('')), 1, 1, ''); -- A,B

5. STUFF 字串插入/替換

STUFF('abc', 2, 1, 'B')  -- 從第 2 字元刪 1 字元並插入 'B' → 'aBc'
-- 常用於移除 FOR XML PATH 產生的前導分隔符

6. 多表 UPDATE

UPDATE t1 SET t1.col = t2.val
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t2.type = 'X';

7. 日期常用

DATEDIFF(HOUR, '2024-01-01 10:00', '2024-01-02 10:00')  -- 24
DATEADD(DAY, 7, GETDATE())  -- 一週後

8. 行列轉換

-- 變量法
DECLARE @s NVARCHAR(MAX) = '';
SELECT @s = @s + ',' + name FROM @t;
SELECT STUFF(@s, 1, 1, '');

-- FOR XML PATH 法 (推薦, 集合式)
SELECT STUFF((SELECT ',' + name FROM @t FOR XML PATH('')), 1, 1, '');

9. 約束管理

-- 查詢約束
EXEC sp_helpconstraint 't';

-- 刪除約束
ALTER TABLE t DROP CONSTRAINT PK_t;

-- 建立預設值約束
ALTER TABLE t ADD CONSTRAINT DF_t_col DEFAULT ('') FOR col;

10. CTE 臨時表與遞迴

-- 臨時結果集
WITH cte AS (SELECT * FROM t WHERE type=1) SELECT * FROM cte;

-- 遞迴階層查詢
WITH dept AS (
    SELECT id, name FROM CostDept WHERE code='0123'
    UNION ALL
    SELECT cd.id, cd.name FROM dept d JOIN CostDept cd ON d.id = cd.parentCode
) SELECT * FROM dept;

11. UPDATE 異常檢查模式

UPDATE t SET col=1 WHERE id=1;
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
BEGIN
    ROLLBACK; PRINT 'Rollback'; RETURN;
END
COMMIT; PRINT 'Commit';

12. 大量資料分批刪除 (避免記錄檔暴增/鎖升級)

DECLARE @start DATE = '2024-01-01', @end DATE = '2024-01-31';
WHILE 1=1
BEGIN
    DELETE TOP (20000) FROM dbo.BigTable
    WHERE CreateDate BETWEEN @start AND @end;
    IF @@ROWCOUNT < 20000 BREAK;
    WAITFOR DELAY '00:00:05'; -- 緩衝
END

13. 跨伺服器操作

-- 建立連結伺服器
EXEC sp_addlinkedserver @server='LNK', @srvproduct='', @provider='SQLNCLI', @datasrc='192.168.1.100';
EXEC sp_addlinkedsrvlogin @server='LNK', @useself='FALSE', @locallogin=NULL, @rmtuser='sa', @rmtpassword='pwd';

-- 查詢
SELECT * FROM [LNK].[DB].[dbo].[Table];

-- 匯入
SELECT * INTO LocalTable FROM [LNK].[DB].[dbo].[Table];

-- 更新遠端
UPDATE r SET r.col = l.col
FROM [LNK].[DB].[dbo].[Table] r JOIN LocalTable l ON r.id = l.id;

-- 移除連結伺服器
EXEC sp_dropserver 'LNK', 'droplogins';

OPENROWSET / OPENDATASOURCE 無連結伺服器即時查詢

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=192.168.1.100;UID=sa;PWD=pwd;', 'SELECT * FROM DB.dbo.Table');
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=192.168.1.100;User ID=sa;Password=pwd').DB.dbo.Table;