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 WHERE oRemote.name IS NULL ORDER BY oLocal.type_desc, oLocal.name
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:
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:
Next, we want to find new or changed columns in existing objects. Here’s how:
SELECT oLocal.type_desc, oLocal.name, 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 cLocal.name = cRemote.name COLLATE DATABASE_DEFAULT WHERE cRemote.name IS NULL ORDER BY oLocal.type_desc, oLocal.name, 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:
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.