หมวดหมู่: T-SQL

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;