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:

SELECT oLocal.*
FROM master.sys.all_objects oLocal
LEFT OUTER JOIN MyOldServer.master.sys.all_objects oRemote
ON oLocal.object_id = oRemote.object_id
ORDER BY oLocal.type_desc,

We’re getting all local system objects that don’t have a matching system object on the other (remote) server. The “MyOldServer” 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:

SELECT oLocal.type_desc,, cLocal.*
FROM master.sys.all_objects oLocal
INNER JOIN master.sys.all_columns cLocal ON oLocal.object_id = cLocal.object_id
INNER JOIN MyOldServer.master.sys.all_objects oRemote ON oLocal.object_id = oRemote.object_id
LEFT OUTER JOIN MyOldServer.master.sys.all_columns cRemote
ON oLocal.object_id = cRemote.object_id AND cLocal.column_id = cRemote.column_id --AND = COLLATE DATABASE_DEFAULT
ORDER BY oLocal.type_desc,, cLocal.column_id

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.

Brent Ozar
I make Microsoft SQL Server faster and more reliable. I love teaching, travel, and laughing.

I’m mostly a figurehead here at Brent Ozar Unlimited. My true skills are menu advice, interpretive dance, and reading Wikipedia.
Brent Ozar on sabtwitterBrent Ozar on sablinkedinBrent Ozar on sabinstagramBrent Ozar on sabgoogleBrent Ozar on sabfacebook
↑ Back to top
  1. 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!

  2. 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

  3. Pingback: Something for the Weekend - SQL Server Links 05/04/13 • John Sansom

  4. 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 *