Catching SQL Server System Object Changes

SQL Server

Let’s say you get your hands on a brand new version of SQL Server that you haven’t played with before, and you want to know what system objects have changed – DMVs, DMFs, system stored procs, yadda yadda yadda. Sure, you could read the documentation – but ain’t nobody got time for that, and they’re probably leaving out the juicy stuff, right? Here’s what I do (thanks to improvement suggestions from Kendra):

First, create a linked server between the new version and the old version. This lets us query across servers and join things together. In my examples, I’ll be running my queries on the new SQL Server, which I’ll be calling local, and the linked server will point to the remote server.

Identify new system objects with this query:

We’re getting all local system objects that don’t have a matching system object on the other (remote) server. The “” is the server name for our older SQL Server box that we’re comparing against – our linked server name. The results give you the new functions, stored procs, you name it:

Ooo! New Shiny System Objects!

We’re joining on object_id, but that’s a gamble – Microsoft could use different object_ids between versions if they wanted to. If we wanted to filter those out, we could join on name, schema_id, and type_desc (view, stored proc, etc), but that gets a little sketchy. For example, there’s now two CHECK_CONSTRAINTS views in SQL Server 2012 with different object_ids, and we wouldn’t catch that if we were only joining on names. In the below screenshot, the top result set is from SQL Server 2012, and the bottom is from SQL Server 2008R2:

One of these servers is not like the others, one of these servers just doesn’t belong

Next, we want to find new or changed columns in existing objects. Here’s how:

Note that we’ve commented out a join on name – if you’re worried that existing columns might have changed names, that’s how you’d catch it. The results:

I see what you did there.

Presto – we can see what new instrumentation fields Microsoft is giving us. This helps script authors do a better job of testing their changes across multiple versions. When someone sends me an sp_Blitz® improvement that relies on a DMV or field I’m not familiar with, I can quickly check multiple servers to see if it’s available across all of my lab servers, or which version it appeared in.

Here are several scripts that can catch all kinds of changes:


Previous Post
Choosing a Presentation Tool
Next Post
DBAs vs Devs: ORMs, Caching & Access to Prod

4 Comments. Leave new

  • I tend to use BOL to see about version differences but it’s somewhat clunky. I think I’m going to try to set this up on my test lab. Thanks for the idea!

  • You could also add in the IsMSShipped property to make sure that the objects you are looking at are actually from Microsoft. objectproperty(object_id,’IsMSShipped’) = 1

  • Kelly (SQLK)
    April 9, 2013 11:06 am

    Sweet Article Brent… it is such a great and simple approach; brillant. I am ditching a meeting just so I can try this out today. Thanks as always, you never disappoint.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.