GET Database diffrence

----Declaration
BEGIN 
 DECLARE @Status_TableNotFound  INT = 1
 DECLARE @Status_FieldNotFound  INT = 2
 DECLARE @Status_FieldTypeChnage  INT = 3
 DECLARE @Status_FieldLengthChange INT = 4
ENd

--Get Table / Filed List
BEGIN
 SELECT 
  --- db1
  db1Objects.id AS db1Id
  , db1Objects.name AS db1TableName
  , db1Column.name AS db1FieldName 
  , db1Column.xType as db1FieldType 
  , UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
  , db1Column.length as db1FieldLength
  , db1Column.ColStat & 1 AS db1IsIdentity

  --- db2
  , ISNULL( db2Objects.id, 0) AS db2Id
  , ISNULL( db2Objects.name, '') AS db2TableName
  , ISNULL( db2Column.name, '') AS db2FieldName 
  , ISNULL( db2Column.xType, 0 ) as db2FieldType 
  , UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
  , ISNULL( db2Column.length, 0 ) as db2FieldLength
  , ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
  , 0 AS Status
 INTO #FieldCheck
 FROM <DBName1>.dbo.SysObjects AS db1Objects 
  INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
   AND db1Objects.xtype = 'U'
   AND db1Objects.category = 0
  
  LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
   AND db2Objects.xtype = 'U'
   AND db2Objects.category = 0
  LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
   AND db2Column.name = db1Column.name
END

--- Check for Status
BEGIN
 --- TableNotFound
 UPDATE #FieldCheck
 SET Status = @Status_TableNotFound
 WHERE db1TableName != db2TableName
 
 --- FieldNotFound
 UPDATE #FieldCheck
 SET Status = @Status_FieldNotFound
 WHERE db1FieldName != db2FieldName
  AND Status = 0
 
 --- FieldTypeChnage
 UPDATE #FieldCheck
 SET Status = @Status_FieldTypeChnage
 WHERE db1FieldName = db2FieldName
  AND db1FieldTypeName != db2FieldTypeName
  AND Status = 0
 
 --- FieldLengthChange
 UPDATE #FieldCheck
 SET Status = @Status_FieldLengthChange
 WHERE db1FieldLength != db2FieldLength
  AND Status = 0
END


--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound

--- FieldNotFound
SELECT DISTINCT 
 db1TableName AS TableName
 , db1FieldName FieldNotFound
 , 'ALTER TABLE ' + db1TableName + 
  ' ADD ' + db1FieldName + 
  ' ' + db1FieldTypeName + 
  ' ' + ( 
    CASE 
     WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
      THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
     ELSE ''
    END
        ) +
  '; UPDATE ' + db1TableName +
  ' SET  ' + db1FieldName + ' = ' +
  (
   CASE
    WHEN db1FieldTypeName LIKE '%CHAR%' 
      OR db1FieldTypeName LIKE '%TEXT%' 
      OR db1FieldTypeName LIKE '%XML%' 
     THEN ''''''
    WHEN db1FieldTypeName LIKE 'INT' 
      OR db1FieldTypeName LIKE 'SMALLINT'
      OR db1FieldTypeName LIKE 'BIGINT'
      OR db1FieldTypeName LIKE '%BINARY%'
      OR db1FieldTypeName LIKE 'float'
      OR db1FieldTypeName = 'BIT'
     THEN '0'
    ELSE 'object'
   END
  )
  ' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound

--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage


--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange

DROP TABLE #FieldCheck