SELECT [@@Version] = @@VERSION;
SELECT [Internal DB Version] = DATABASEPROPERTYEX(N'msdb', N'Version');
 
/*
 
@@Version
-----------------------------------------------------
Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64) 
	May 20 2022 13:29:42 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows ...
 
Internal DB Version
-----------------------------------------------------
950
 
*/

DECLARE @srv sysname = N'.\SQL2019';
 
EXEC [master].dbo.sp_addlinkedserver   
     @server     = @srv, 
     @srvproduct = N'SQL Server';
 
EXEC [master].dbo.sp_addlinkedsrvlogin 
     @rmtsrvname = @srv, 
     @useself    = N'True';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'collation compatible', 
     @optvalue   = N'true';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'data access', 
     @optvalue   = N'true';

USE [master];
GO
 
CREATE SYNONYM dbo.old_databases FOR [.\SQL2019].master.sys.databases;
CREATE SYNONYM dbo.old_objects   FOR [.\SQL2019].master.sys.all_objects;
CREATE SYNONYM dbo.old_columns   FOR [.\SQL2019].master.sys.all_columns;
CREATE SYNONYM dbo.old_modules   FOR [.\SQL2019].master.sys.all_sql_modules;
CREATE SYNONYM dbo.old_sessions  FOR [.\SQL2019].master.sys.server_event_sessions;
CREATE SYNONYM dbo.old_xevents   FOR [.\SQL2019].master.sys.server_event_session_events;
CREATE SYNONYM dbo.old_perf      FOR [.\SQL2019].master.sys.dm_os_performance_counters;
CREATE SYNONYM dbo.old_messages  FOR [.\SQL2019].master.sys.messages;

SELECT [Object Name] = N'sys.' + o.name, [Type] = o.[type_desc]
FROM sys.all_objects AS o
WHERE o.[schema_id] = 4 -- we only care about sys.
AND o.[type_desc] <> N'PRIMARY_KEY_CONSTRAINT'
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_objects AS oo
    WHERE oo.[schema_id] = 4 AND oo.name = o.name
)
ORDER BY o.[type_desc], o.name;

;WITH obj AS
(
  SELECT o.name, o.[object_id]
  FROM sys.all_objects AS o WHERE [schema_id] = 4
  INTERSECT
  SELECT o.name, o.[object_id]
  FROM dbo.old_objects AS o WHERE [schema_id] = 4
),
col(obj,col) AS
(
  SELECT obj.name, c.name 
  FROM obj INNER JOIN sys.all_columns AS c
    ON obj.[object_id] = c.[object_id]
  EXCEPT 
  SELECT obj.name, c.name 
  FROM obj INNER JOIN dbo.old_columns AS c
    ON obj.[object_id] = c.[object_id]
)
SELECT obj = N'sys.' + obj, col 
  FROM col ORDER BY obj, col;

SELECT o.[type_desc], N'sys.' + o.name,
  delta = LEN(m.[definition]) - LEN(rm.[definition]), 
  new_definition = m.[definition],
  old_definition = rm.[definition]
FROM sys.all_sql_modules AS m
INNER JOIN sys.all_objects AS o
  ON m.[object_id] = o.[object_id]
INNER JOIN dbo.old_objects AS ro
  ON o.name = ro.name
INNER JOIN dbo.old_modules AS rm
  ON ro.[object_id] = rm.[object_id]
WHERE  o.[schema_id] = 4
  AND ro.[schema_id] = 4
  AND m.[definition] <> rm.[definition]
ORDER BY o.[type_desc], o.name;

SELECT [Event] = e.package + '.' + e.name, e.event_id
  FROM sys.server_event_sessions AS s
  INNER JOIN sys.server_event_session_events AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
EXCEPT
SELECT e.package + '.' + e.name, e.event_id
  FROM dbo.old_sessions AS s
  INNER JOIN dbo.old_xevents AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
ORDER BY e.package + '.' + e.name, e.event_id;

SELECT m.message_id, m.[text]
FROM sys.messages AS m
WHERE m.language_id = 1033 -- us_english
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_messages AS om
    WHERE om.message_id = m.message_id
);