Table compare

This script generates SQL to diff the data in two tables, based on the columns of one of them.

For example, if you've created a new version of a table , dbo.MyTable_v2, and you want to compare it to the original dbo.MyTable, you can run this script with @tableName = 'dbo.MyTable' .

The generated script refers to two CTEs, old and new – the idea is that you populate each with a SELECT from the two tables for comparison, and can limit the scope of both as required within the CTE definition.

If the tables are very wide (so that the generated script is long) you will need Print big instead of PRINT.

DECLARE @tableName NVARCHAR(255) = 'dbo.MyTable'
 
DECLARE @selectCols NVARCHAR(MAX) = ''
DECLARE @joinCols NVARCHAR(MAX) = ''
DECLARE @orderCols NVARCHAR(MAX) = ''
 
SELECT 
  @selectCols += '
, COALESCE(old.[' + COLUMN_NAME + '], new.[' + COLUMN_NAME + ']) AS [' + COLUMN_NAME + ']'
, @joinCols += '
    AND (old.[' + COLUMN_NAME + '] = new.[' + COLUMN_NAME + '] OR old.[' + COLUMN_NAME + '] IS NULL AND new.[' + COLUMN_NAME + '] IS NULL)'
, @orderCols += '
, COALESCE(old.[' + COLUMN_NAME + '], new.[' + COLUMN_NAME + '])'
FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN sys.objects o ON o.[name] = c.TABLE_NAME
  INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE o.[object_id] = OBJECT_ID(@tableName)
 
DECLARE @sql NVARCHAR(MAX) = ';WITH old AS (
  SELECT ''OLD'' AS __cmp_Version, *
  FROM ...
), new AS (
  SELECT ''NEW'' AS __cmp_Version, *
  FROM ...
)
SELECT
  COALESCE(old.[__cmp_Version], new.[__cmp_Version]) AS [Version]' + @selectCols + '
FROM old 
  FULL OUTER JOIN new 
    ON ' + SUBSTRING(@joinCols, 11, LEN(@joinCols)) + '
WHERE old.[__cmp_Version] IS NULL
OR new.[__cmp_Version] IS NULL
ORDER BY 
  ' + SUBSTRING(@orderCols, 5, LEN(@orderCols))   
 
PRINT @sql

tsql, dynamic