Tag Archive compliance

WITH RequiredColumns AS (
    -- กำหนดรายการคอลัมน์มาตรฐานที่ต้องมี
    SELECT col FROM (VALUES 
        ('id'), 
        ('created_by'), 
        ('created_date'), 
        ('deleted_by'), 
        ('deleted_date'), 
        ('updated_by'), 
        ('updated_date')
    ) AS t(col)
),
TableList AS (
    -- ดึงรายชื่อตารางทั้งหมดที่ไม่ใช่ System Tables
    SELECT 
        t.object_id,
        s.name AS SchemaName,
        t.name AS TableName
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.is_ms_shipped = 0 -- ไม่รวมตารางของระบบ
      AND t.name <> 'sysdiagrams'
),
CheckAudit AS (
    -- ตรวจสอบว่าในแต่ละตาราง มีคอลัมน์ไหนอยู่บ้าง
    SELECT 
        tl.SchemaName,
        tl.TableName,
        rc.col AS RequiredColumn,
        CASE WHEN c.column_id IS NOT NULL THEN 1 ELSE 0 END AS HasColumn
    FROM TableList tl
    CROSS JOIN RequiredColumns rc -- เอาตารางตั้งต้น x รายชื่อคอลัมน์ที่ต้องมี
    LEFT JOIN sys.columns c ON tl.object_id = c.object_id AND rc.col = c.name
)
SELECT 
    SchemaName,
    TableName,
    CASE 
        WHEN MIN(HasColumn) = 1 THEN 'PASS' 
        ELSE 'FAIL' 
    END AS Status,
    -- รวบรวมรายชื่อคอลัมน์ที่ขาด (Missing Columns)
    STUFF((
        SELECT ', ' + ca2.RequiredColumn
        FROM CheckAudit ca2
        WHERE ca2.SchemaName = ca.SchemaName 
          AND ca2.TableName = ca.TableName
          AND ca2.HasColumn = 0
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
    ) AS MissingColumns
FROM CheckAudit ca
GROUP BY SchemaName, TableName
ORDER BY Status, SchemaName, TableName;