Identify System Object Differences Between SQL Server Versions

use tempdb
-- for sys.all_objects
if object_id('dbo.AllObject', 'U') is not null
   drop table dbo.AllObject;
create table dbo.AllObject (
  [schema] sysname
, [object] sysname
, [type] varchar(10)
, [type_desc] sysname
, [version] int
, logdate datetime default current_timestamp
, id int identity primary key);
go
 
-- for sys.all_parameters
if object_id('dbo.AllParam', 'U') is not null
   drop table dbo.AllParam;
create table dbo.AllParam (
  [schema] sysname
, [object] sysname
, [object_type] varchar(10)
, [param] sysname
, [param_type] sysname
, [max_length] int
, [is_output] bit
, [version] int
, logdate datetime default current_timestamp
, id int identity primary key);
 
-- for sys.all_columns
if object_id('dbo.AllColumn', 'U') is not null
   drop table dbo.AllColumn;
create table dbo.AllColumn (
  [schema] sysname
, [object] sysname
, [object_type] varchar(10)
, [column] sysname
, [column_type] sysname
, [max_length] int
, [version] int
, logdate datetime default current_timestamp
, id int identity primary key);
   

# this script is to collect sql server system objects/parameters/columns
# the repository tables need to be created at $Repository_svr first
 
#requires -version 4.0
import-module sqlserver -disable;
#server list, from which I will collect object/parameter/column info
#you may need to change to your own
[string[]]$serverlist = 'localhost','localhost\sql2014','localhost\sql2016', 'localhost\sql2017'; 
 
#this is where repository tables reside on its [TempDB]
#you may need to change to your own
[string]$repository_svr = 'localhost';
 
[string]$targetdb = 'master'; # can be 'msdb'
 
# -- find new objects
$qry = @"
declare @ver varchar(30)= cast(serverproperty('productversion') as varchar(30));
 
select [schema]=schema_name(schema_id), [object]=name, [type]=type, type_desc
     , [version]=cast(substring(@ver, 1, charindex('.',@ver)-1) as int)
from sys.all_objects
where type not in ('IT', 'PK', 'F', 'D', 'C', 'UQ') -- ignore Internal tables/PK/FK/Default/Check/Unique constraints
and is_ms_shipped = 1 -- only for system tables
 
"@;
 
# -- find new parameters for SPs/Functions
$qry2=@"
declare @ver varchar(30)= cast(serverproperty('productversion') as varchar(30));
 
select [schema]=schema_name(o.schema_id), [object]=object_name(p.object_id)
, [object_type]=o.type, [param]=p.name, [param_type]= t.name, p.max_length, p.is_output
, [version]=cast(substring(@ver, 1, charindex('.',@ver)-1) as int)
from sys.all_parameters p
inner join sys.types t
on p.system_type_id = t.system_type_id
and p.user_type_id = t.user_type_id
inner join sys.all_objects o
on p.object_id = o.object_id;
"@
 
# -- find new columns for views/tables
$qry3 = @"
declare @ver varchar(30)= cast(serverproperty('productversion') as varchar(30));
 
select [schema]=schema_name(o.schema_id), [object]=object_name(o.object_id)
, [object_type]=o.type, [column]=c.name, [column_type]= t.name, c.max_length
, [version]=cast(substring(@ver, 1, charindex('.',@ver)-1) as int)
from sys.all_columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
and c.user_type_id = t.user_type_id
inner join sys.all_objects o
on c.object_id = o.object_id;
"@
 
foreach ($s in $serverlist)
{
    #collect objects
    $r = invoke-sqlcmd -Server $s -database $targetdb -query $qry -as DataTables;
    #write to repository table
    write-sqltabledata -server $repository_svr -DatabaseName tempdb -SchemaName dbo -TableName AllObject -InputData $r ;
 
    #collect parameters
    $r = invoke-sqlcmd -Server $s -database $targetdb -query $qry2 -as DataTables;
    #write to repository table
    write-sqltabledata -server $repository_svr -DatabaseName tempdb -SchemaName dbo -TableName AllParam -InputData $r;
 
    #collect columns
    $r = invoke-sqlcmd -Server $s -database $targetdb -query $qry3 -as DataTables;
    #write to repository table
    write-sqltabledata -server $repository_svr -DatabaseName tempdb -SchemaName dbo -TableName AllColumn -InputData $r;
}
   

use tempdb 
-- find what system objects are ADDED between sql server 2017 and 2012
select [schema], [object], [type] from dbo.AllObject where [version] = 14 -- sql server 2017
except
select [schema], [object], [type] from dbo.AllObject where [version] = 11 -- sql server 2012
go
 
-- find what system objects are DROPPED between sql server 2017 and 2012
select [schema], [object], [type] from dbo.AllObject where [version] = 11 -- sql server 2012
except
select [schema], [object], [type] from dbo.AllObject where [version] = 14 -- sql server 2017
go
   

-- find new parameters added in existing proc/func
use TempDB;
; with c as (
select distinct p1.id, p1.[schema], p1.[object], p1.[object_type]--, p2.[object_type]
from dbo.AllParam p1 
inner join dbo.AllParam p2
on p1.[schema]=p2.[schema]
and p1.[object]=p2.[object]
and p1.[version]=14  -- sql server 2017
and p2.[version]=11  -- sql server 2012
)
select [Object]=p1.[schema]+'.'+p1.[Object] 
, p1.[param], p1.param_type, p1.max_length,p1.is_output, p1.[version]
from dbo.AllParam p1
inner join c
on c.id=p1.id
left join dbo.AllParam p2
on p2.[schema]=p1.[schema] and p2.object = p1.object
and p2.[param]=p1.[param]
and p2.[version]=11
where p2.[param] is null;
   

-- find new columns in sql server 2017 (compared to sql server 2012)
; with c as (
select distinct o.id, o.[schema], o.[object], o.[type], o.[version]
from dbo.AllObject o 
inner join dbo.AllObject o2
on  o.[schema]=o2.[schema]
and o.[object]=o2.[object]
and o.[version]=14  -- sql server 2017
and o2.[version]=11 -- sql server 2012
)
select [Object]=ac.[schema]+'.'+ac.[Object] 
, ac.[column], ac.column_type, ac.max_length, ac.[version]
from dbo.AllColumn ac
inner join c
on c.[object]=ac.[object] and c.[schema]=ac.[schema] and c.[version]=ac.[version]
left join dbo.AllColumn ac2
on  ac2.[schema]=ac.[schema] and ac2.object = ac.object
and ac2.[column]=ac.[column]
and ac2.[version]=11
where ac2.[column] is null;
   

-- find new column count group by object in sql server 2017 (compared to sql server 2012)
; with c as (
select distinct o.id, o.[schema], o.[object], o.[type], o.version--, p2.[object_type]
from dbo.AllObject o 
inner join dbo.AllObject o2
on  o.[schema]=o2.[schema]
and o.[object]=o2.[object]
and o.[version]=14  -- sql server 2017
and o2.[version]=11 -- sql server 2012
), w as (
select [Object]=ac.[schema]+'.'+ac.[Object] 
, ac.[column], ac.column_type, ac.max_length, ac.[version]--, c.[version]
from dbo.AllColumn ac
inner join c
on c.[object]=ac.[object] and c.[schema]=ac.[schema] and c.[version]=ac.[version]
left join dbo.AllColumn ac2
on  ac2.[schema]=ac.[schema] and ac2.object = ac.object
and ac2.[column]=ac.[column]
and ac2.[version]=11
where ac2.[column] is null)
select [object], NewColumnCount=count(*)
from w
group by [object]
order by [object];