SQL Server Schema Differences

The following SQL statement will generate a crude list of column differences between two SQL Server databases:

DECLARE @oldDatabase NVARCHAR(MAX)
DECLARE @newDatabase NVARCHAR(MAX)

SET @oldDatabase = 'unittestnew_3_14_x'
SET @newDatabase = 'unittestnew_3_15_x'

DECLARE @sql NVARCHAR(MAX)
SET @sql = '
WITH
  oldColumns AS (
    SELECT
      tab.name AS tableName
    , ROW_NUMBER() OVER (PARTITION BY tab.name ORDER BY col.column_id) AS columnSeq
    , col.name AS columnName
    , typ.name AS type
    , col.max_length AS length
    , col.scale AS scale
    FROM ' + @oldDatabase + '.sys.all_columns AS col
    INNER JOIN ' + @oldDatabase + '.sys.all_objects AS tab
      ON tab.object_id = col.object_id
      AND tab.type = ''U''
    INNER JOIN ' + @oldDatabase + '.sys.schemas AS sch
      ON sch.schema_id = tab.schema_id
      AND sch.name = ''dbo''
    INNER JOIN ' + @oldDatabase + '.sys.systypes AS typ
      ON typ.xusertype = col.system_type_id
  )
, newColumns AS (
    SELECT
      tab.name AS tableName
    , ROW_NUMBER() OVER (PARTITION BY tab.name ORDER BY col.column_id) AS columnSeq
    , col.name AS columnName
    , typ.name AS type
    , col.max_length AS length
    , col.scale AS scale
    FROM ' + @newDatabase + '.sys.all_columns AS col
    INNER JOIN ' + @newDatabase + '.sys.all_objects AS tab
      ON tab.object_id = col.object_id
      AND tab.type = ''U''
    INNER JOIN ' + @newDatabase + '.sys.schemas AS sch
      ON sch.schema_id = tab.schema_id
      AND sch.name = ''dbo''
    INNER JOIN ' + @newDatabase + '.sys.systypes AS typ
      ON typ.xusertype = col.system_type_id
  )
, matched AS (
    SELECT
      COALESCE(old.tableName, new.tableName) AS tableName
    , old.columnSeq AS oldColumnSeq
    , old.columnName AS oldColumnName
    , old.type AS oldType
    , old.length AS oldLength
    , old.scale AS oldScale
    , new.columnSeq AS newColumnSeq
    , new.columnName AS newColumnName
    , new.type AS newType
    , new.length AS newLength
    , new.scale AS newScale
    FROM oldColumns AS old
    FULL OUTER JOIN newColumns AS new
      ON new.tableName = old.tableName
      AND new.columnName = old.columnName
  )
SELECT
  tableName
, oldColumnName
, oldType
, oldLength
, oldScale
, CASE
    WHEN oldColumnName IS NULL THEN ''added''
    WHEN newColumnName IS NULL THEN ''deleted''
    ELSE ''changed''
  END AS diff
, newColumnName
, newType
, newLength
, newScale
FROM matched
WHERE oldColumnName IS NULL
OR newColumnName IS NULL
OR oldColumnName <> newColumnName
OR oldType <> newType
OR oldLength <> newLength
OR oldScale <> newScale
ORDER BY tableName
, COALESCE(oldColumnSeq, newColumnSeq)
'

EXEC sp_executesql @sql