Change the oldDb and newDb parameters and execute the query.
This will tell you also which columns were modified, which are new and which does not exist in the old database.
DECLARE @oldDb as nvarchar(max) = 'sourceDb';
DECLARE @newDb as nvarchar(max) = 'targetDb';
DECLARE @sql as nvarchar(max);
SET @sql = 'SELECT CASE WHEN t2.COLUMN_NAME IS NULL THEN ''New'' ELSE ''Modified'' END as ''Status'',
t1.TABLE_CATALOG+''.''+t1.TABLE_SCHEMA+''.''+t1.TABLE_NAME+''.''+t1.COLUMN_NAME as ''OldColumn'',
t1.COLUMN_DEFAULT,t2.COLUMN_DEFAULT as ''NewDefault'',
t1.IS_NULLABLE,t2.IS_NULLABLE as ''NewNullable'',
t1.DATA_TYPE,t2.DATA_TYPE as ''NewType'',
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH as ''NewLength''
FROM ['+@oldDb+'].[INFORMATION_SCHEMA].[COLUMNS] t1
LEFT JOIN ['+@newDb+'].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL
OR ( t1.TABLE_NAME = t2.TABLE_NAME
AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
AND t1.COLUMN_NAME = t2.COLUMN_NAME
AND ( t1.DATA_TYPE != t2.DATA_TYPE
OR t1.IS_NULLABLE != t2.IS_NULLABLE
OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
UNION
SELECT ''NotInSource'' as ''Status'',
t1.TABLE_CATALOG+''.''+t1.TABLE_SCHEMA+''.''+t1.TABLE_NAME+''.''+t1.COLUMN_NAME as ''OldColumn'',
t1.COLUMN_DEFAULT,t2.COLUMN_DEFAULT as ''NewDefault'',
t1.IS_NULLABLE,t2.IS_NULLABLE as ''NewNullable'',
t1.DATA_TYPE,t2.DATA_TYPE as ''NewType'',
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH as ''NewLength''
FROM ['+@newDb+'].[INFORMATION_SCHEMA].[COLUMNS] t1
LEFT JOIN ['+@oldDb+'].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL'
EXEC sp_executesql @sql;
Also you can compare the modified procedures from a database to another with the following request :
DECLARE @oldDb as nvarchar(max) = 'sourceDb';
DECLARE @newDb as nvarchar(max) = 'targetDb';
DECLARE @sql as nvarchar(max);
SET @sql = 'SELECT
CASE WHEN proc2.modify_date > proc1.modify_date
THEN ''Modified-OnTarget''
ELSE CASE WHEN proc2.modify_date < proc1.modify_date
THEN ''Modified-OnSource''
ELSE ''New'' END END as ''Status''
, proc1.name
, CASE WHEN proc2.modify_date > proc1.modify_date
THEN proc2.modify_date
ELSE proc1.modify_date END as ''Modified''
, CASE WHEN proc2.modify_date > proc1.modify_date
THEN rout2.[ROUTINE_DEFINITION]
ELSE rout1.[ROUTINE_DEFINITION] END as ''Definition''
FROM ['+@oldDb+'].sys.procedures proc1
LEFT JOIN ['+@newDb+'].sys.procedures proc2
ON proc1.name = proc2.name
LEFT JOIN ['+@oldDb+'].[INFORMATION_SCHEMA].[ROUTINES] rout1
ON proc1.name = rout1.[SPECIFIC_NAME]
AND rout1.[ROUTINE_TYPE] = ''PROCEDURE''
LEFT JOIN ['+@newDb+'].[INFORMATION_SCHEMA].[ROUTINES] rout2
ON proc2.name = rout2.[SPECIFIC_NAME]
AND rout2.[ROUTINE_TYPE] = ''PROCEDURE''
AND proc2.type_desc = ''SQL_STORED_PROCEDURE''
WHERE proc1.type_desc = ''SQL_STORED_PROCEDURE''
AND (proc2.object_id IS NULL
OR rout1.[ROUTINE_DEFINITION] != rout2.[ROUTINE_DEFINITION])
ORDER BY 1 '
EXEC sp_executesql @sql;