20260511mon-02

前回（01）は手書きで UNION ALL を並べました。
今回はテーブル名・カラム名をリストで管理し、動的に SQL 文字列を組み立てて実行する方法を学びます。

完成した SQL

DECLARE @sql   NVARCHAR(MAX)
DECLARE @where NVARCHAR(512) = N'([借方金額] <> '''' OR [貸方金額] <> '''') AND [摘要] <> '''''

;WITH 対象テーブル AS (
    SELECT [テーブル名]
    FROM (VALUES
        (N'テーブルA'),
        (N'テーブルB'),
        (N'テーブルC')
    ) AS t([テーブル名])
),
条件カラム AS (
    SELECT [カラム名]
    FROM (VALUES
        (N'借方金額'),
        (N'貸方金額'),
        (N'摘要')
    ) AS c([カラム名])
)
SET @sql = STUFF((
    SELECT N' UNION ALL ' +
        N'SELECT ''' + t.[テーブル名] + N''' AS [テーブル名], [システム名], ' +
        STUFF((
            SELECT N', [' + [カラム名] + N']'
            FROM 条件カラム
            FOR XML PATH('')
        ), 1, 2, '') +
        N' FROM ' + t.[テーブル名] +
        N' WHERE ' + @where
    FROM 対象テーブル AS t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, LEN(N' UNION ALL '), '')

SELECT @sql
EXEC sp_executesql @sql

① DECLARE — 変数の準備

DECLARE @sql   NVARCHAR(MAX)
DECLARE @where NVARCHAR(512) = N'([借方金額] <> '''' OR [貸方金額] <> '''') AND [摘要] <> '''''

@where に出てくる '''' は動的 SQL 用のクォートエスケープです。
T-SQL の文字列リテラル内で ' を1文字表すには '' と書きます。
動的 SQL の中で「空文字比較 ''」を埋め込むには、' を2つ分エスケープするので '''' になります。

@where に実際に格納される値:

([借方金額] <> '' OR [貸方金額] <> '') AND [摘要] <> ''

意味：「借方金額か貸方金額のどちらかに値がある」かつ「摘要が空でない」行を残すフィルタ。

② CTE — テーブル名・カラム名をリストで定義

;WITH 対象テーブル AS (
    SELECT [テーブル名]
    FROM (VALUES
        (N'テーブルA'),
        (N'テーブルB'),
        (N'テーブルC')
    ) AS t([テーブル名])
),
条件カラム AS (
    SELECT [カラム名]
    FROM (VALUES
        (N'借方金額'),
        (N'貸方金額'),
        (N'摘要')
    ) AS c([カラム名])
)

VALUES (...) を FROM 句で使うと、その場でテーブルのようなデータを作れます。
テーブルや列を増減したいときは、ここのリストを編集するだけで対応できます。

③ 内側の STUFF — カラムリスト文字列を作る

STUFF((
    SELECT N', [' + [カラム名] + N']'
    FROM 条件カラム
    FOR XML PATH('')
), 1, 2, '')

FOR XML PATH('') は SELECT の各行をそのまま1本の文字列に連結します。
3行連結した直後の文字列:

, [借方金額], [貸方金額], [摘要]

先頭に , が余るので STUFF(..., 1, 2, '') で1文字目から2文字を空文字に置換（＝削除）します。

[借方金額], [貸方金額], [摘要]

STUFF(文字列, 開始位置, 削除文字数, 置換文字列) — 開始位置は 1 始まり。

④ 外側の STUFF — テーブルごとに SELECT 文を組み立てる

SET @sql = STUFF((
    SELECT N' UNION ALL ' +
        N'SELECT ''' + t.[テーブル名] + N''' AS [テーブル名], [システム名], '
        + <③のカラムリスト>
        + N' FROM ' + t.[テーブル名]
        + N' WHERE ' + @where
    FROM 対象テーブル AS t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, LEN(N' UNION ALL '), '')

対象テーブル の各行（テーブルA・B・C）に対してそれぞれ SELECT 文を組み立て、FOR XML PATH('') で連結します。
連結後の文字列の先頭には UNION ALL が残るので、STUFF(..., 1, LEN(N' UNION ALL '), '') で先頭の UNION ALL を削除して完成形にします。

.value('.', 'NVARCHAR(MAX)') は、FOR XML PATH が XML 特殊文字を自動エスケープするのを防ぐための処理です。TYPE で XML 型にしてから .value() で文字列に戻すことで、& などの意図しない変換を避けます。

⑤ SELECT @sql / EXEC — 確認と実行

SELECT @sql
EXEC sp_executesql @sql

SELECT @sql で組み立てた SQL 文字列を目視確認できます。
EXEC sp_executesql @sql で文字列として組み立てた SQL を実際に実行します。

@sql に格納される SQL（整形後）:

SELECT 'テーブルA' AS [テーブル名], [システム名], [借方金額], [貸方金額], [摘要]
FROM テーブルA
WHERE ([借方金額] <> '' OR [貸方金額] <> '') AND [摘要] <> ''
UNION ALL
SELECT 'テーブルB' AS [テーブル名], [システム名], [借方金額], [貸方金額], [摘要]
FROM テーブルB
WHERE ([借方金額] <> '' OR [貸方金額] <> '') AND [摘要] <> ''
UNION ALL
SELECT 'テーブルC' AS [テーブル名], [システム名], [借方金額], [貸方金額], [摘要]
FROM テーブルC
WHERE ([借方金額] <> '' OR [貸方金額] <> '') AND [摘要] <> ''

まとめ

件数だけ確認する場合

DECLARE @sql   NVARCHAR(MAX)
DECLARE @where NVARCHAR(512) = N'([借方金額] <> '''' OR [貸方金額] <> '''') AND [摘要] <> '''''

;WITH 対象テーブル AS (
    SELECT [テーブル名]
    FROM (VALUES
        (N'テーブルA'),
        (N'テーブルB'),
        (N'テーブルC')
    ) AS t([テーブル名])
)
SET @sql = STUFF((
    SELECT N' UNION ALL ' +
        N'SELECT ''' + t.[テーブル名] + N''' AS [テーブル名], COUNT(*) AS [件数]' +
        N' FROM ' + t.[テーブル名] +
        N' WHERE ' + @where
    FROM 対象テーブル AS t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, LEN(N' UNION ALL '), '')

SELECT @sql
EXEC sp_executesql @sql