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
