PlusMagi's Blog By Pitt Phunsanit SQL Server,T-SQL MSSQL: compare column in table databases

MSSQL: compare column in table databases

เพราะว่ากำลังดู project ใหญ่ ๆ ที databases หลายตัวแถมยังซอยเป็น dev, stg, qa ซึ่ง sq, dev หลายคนเข้าไปแก้ โครงสร้างได้เอง ทำให้เวลาเอา code ขี้น deploy ไปแล้วแตกเพราะ column ดันไม่ตรงกัน เลยเขียน query มาเที่ยบมันซะเลย ได้จบ ๆ
compare_structure_in_databases_v.1.sql

-- Declare variables for the database names
DECLARE @db1 NVARCHAR(128) = 'DB_DEV';
DECLARE @db2 NVARCHAR(128) = 'DB_QA';

-- Use dynamic SQL to build the query
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
 SELECT
  ISNULL(c1.TABLE_SCHEMA, c2.TABLE_SCHEMA) AS TableSchema,
  ISNULL(c1.TABLE_NAME, c2.TABLE_NAME) AS TableName,
  ISNULL(c1.COLUMN_NAME, c2.COLUMN_NAME) AS ColumnName,
  CASE
   WHEN c1.COLUMN_NAME IS NULL THEN ''Missing in ' + QUOTENAME(@db1) + '''
   WHEN c2.COLUMN_NAME IS NULL THEN ''Missing in ' + QUOTENAME(@db2) + '''
   WHEN c1.DATA_TYPE <> c2.DATA_TYPE THEN ''Data Type Mismatch''
   WHEN c1.IS_NULLABLE <> c2.IS_NULLABLE THEN ''Nullability Mismatch''
   ELSE ''Difference''
  END AS DifferenceType,
  c1.DATA_TYPE AS ' + @db1 + '_DataType,
  c2.DATA_TYPE AS ' + @db2 + '_DataType,
  c1.IS_NULLABLE AS ' + @db1 + '_IsNullable,
  c2.IS_NULLABLE AS ' + @db2 + '_IsNullable
 FROM ' + QUOTENAME(@db1) + '.INFORMATION_SCHEMA.COLUMNS c1
 FULL OUTER JOIN ' + QUOTENAME(@db2) + '.INFORMATION_SCHEMA.COLUMNS c2
  ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA
  AND c1.TABLE_NAME = c2.TABLE_NAME
  AND c1.COLUMN_NAME = c2.COLUMN_NAME
 WHERE (
   c1.COLUMN_NAME IS NULL
   OR c2.COLUMN_NAME IS NULL
   OR c1.DATA_TYPE <> c2.DATA_TYPE
   OR c1.IS_NULLABLE <> c2.IS_NULLABLE
  )
  -- Filter the results
  -- AND ISNULL(c1.TABLE_NAME, c2.TABLE_NAME) = ''PITT''
;';

-- Execute the dynamic SQL
EXEC sp_executesql @sql;

มันจะบอกว่าคอลัมน์ไหน ต่างกันที่ตรงไหนบ้าง ช่วยให้เตรียมตัวได้ตอน deploy ไม่โดน QA ตีกลับมา
ในบรรทัด 36 ถ้า uncomment หรือเปลี่ยนเงื่อนไขเพื่อหากลุ่มตารางเฉพาะของเราก็ได้เหมือนกัน