Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free Webinar - Instantly Visualize Your T-SQL Query Results with Charts and Graphs
 

Identify System Object Differences Between SQL Server Versions


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations

Problem

I am currently using SQL Server 2012 and we are considering an upgrade to SQL Server 2017.  I am doing some research about SQL Server 2017, is there any quick way for me to learn what is new or changed to system objects from SQL Server 2012 to SQL Server 2017?

Solution

Every time, a new SQL Server version is released, we can always find a section in BOL titled “What’s new in SQL Server 20xx?”. For SQL Server 2017, we can see this link here. No doubt, this is the most authoritative and comprehensive list, but there is an issue, the “what is new” is usually referring to the changes since the last version. To understand what is new since two+ versions ago, we usually have to read the “What’s new” section one version after another.

In this tip, we will look at how to programmatically check the differences between SQL Server versions. The check mainly focuses on the following aspects (for database engine only):

  1. System Object Changes (i.e. new objects added and old objects dropped)
  2. Parameters Changes (i.e. new parameters for the existing procedures/functions. For compatibility, we should not see parameters dropped from procedures/functions in a new version)
  3. Column Changes (i.e. new columns added for the existing system views/tables. For compatibility, we should not see columns dropped from system views/tables in a new version)

Solution Algorithm

For each SQL Server instance, we will query sys.all_objects, sys.all_parameters and sys.all_columns in the [master] database and retrieve the needed information and dump the data into a repository database. Then we can query the differences among the different versions.

We will use PowerShell and the latest SQL Server PowerShell module to do the work.

Creating Repository Tables

We first create some repository tables to store the collected data. Since these tables are temporary, we will just create them in the [TempDB] database on a repository SQL Server instance. In my case, I created these tables in my default instance on my laptop.

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);
   

Creating Collection PowerShell Script

On my laptop, I have installed four SQL Server instances (Developer Edition):

SQL Instance Version
Localhost (default instance) SQL Server 2012
Localhost\sql2014 SQL Server 2014
Localhost\sql2016 SQL Server 2016
Localhost\sql2017 SQL Server 2017

I will run the following PowerShell script in a PowerShell IDE window to collect the needed info. 

# 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;
}
   

Analyzing the Collection Results

Once I get the three repository tables populated, I can do some analysis.

1) What new system objects are created / dropped between SQL Server 2012 and SQL Server 2017?

The following two queries will answer this question

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
   

We will get the following:

object changes in sql2017 vs sql2012 - Description: new objects and dropped objects between sql2017 and sql2012

In the first result set, we get 193 rows, i.e. there are 193 new system objects added in SQL Server 2017 since SQL Server 2012. In the second result set, there are 3 rows, i.e. three system objects are dropped in SQL Server 2017 while they exist in SQL Server 2012. You can change the [version] value in the query to compare objects between different SQL Server versions.

2) What new parameters are added to existing procedures/functions?

The following query will list the new parameters added to the existing SPs/functions:

-- 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;
   

The result is:

new parameters in sql2017 vs sql2012

We can see a few parameters added to the [sp_spaceused] stored procedure, and [sp_spaceused] is no doubt a frequently used SP by DBAs. From here, we can do further reading on BOL and figure out the exact usage/meaning of these new parameters to this SP.

3) What are the new columns added to existing system tables/views?

The following query will answer this:

-- 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;
   

The result is (267 rows returned):

new columns in sql2017 vs sql2012

We can even do a summary of these rows by seeing how many new columns each object has using the following query:

-- 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];
   

We will get the following:

new columns per object - Description: new columns count per object in sql server 2017 compared to sql server 2012.

We see there are 67 views with new columns in SQL Server 2017 compared with SQL Server 2012.

Summary

In this tip, we have explored how to find system object changes between SQL Server versions. It is very useful when we do research and further study of a new SQL Server version and we can find clues to the new features with the new and changed system objects.

With the collected data, we may need do some other data analysis other than what is shown in this tip. For example, we can query how many objects each type has in different versions, or we can query the trend of discrepancies from this version to the next and to the next.

In this tip, I only collected / compared the system object information from the [master] database, but you can also collect data from the [msdb] database to do comparisons.

Next Steps

I mainly deal with meta data in this tip, but there is another area that is worthwhile to look into. We know there are a few DMVs that may be populated with different values in different versions, some of these DMVs are:

We can easily extract our needed info from these DMVs and dump them into a central repository database with PowerShell and then do some comparison / analysis. It is fun to see how SQL Server is evolving.

You can read the following articles to check what is new in SQL Server and then compare the results from the approach in this tip.



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools