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:
|
1 2 3 4 5 6 7 8 9 |
/* Objects - master database */ SELECT oNew.* FROM [172.16.189.131].master.sys.all_objects oNew LEFT OUTER JOIN master.sys.all_objects oOld ON oNew.object_id = oOld.object_id AND oNew.is_ms_shipped = 1 WHERE oOld.name IS NULL AND oNew.is_ms_shipped = 1 ORDER BY oNew.type_desc, oNew.name; GO |
We’re getting all local system objects that don’t have a matching system object on the other (remote) server. The “172.16.189.131” 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
/* New columns in existing objects - master */ SELECT oNew.type_desc, oNew.name, cNew.* FROM [172.16.189.131].master.sys.all_objects oNew INNER JOIN [172.16.189.131].master.sys.all_columns cNew ON oNew.object_id = cNew.object_id INNER JOIN master.sys.all_objects oOld ON oNew.object_id = oOld.object_id LEFT OUTER JOIN master.sys.all_columns cOld ON cNew.object_id = cOld.object_id AND cNew.column_id = cOld.column_id --AND cLocal.name = cRemote.name COLLATE DATABASE_DEFAULT WHERE cOld.name IS NULL AND oNew.is_ms_shipped = 1 ORDER BY oNew.type_desc, oNew.name, cNew.column_id; GO |
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.
Here are several scripts that can catch all kinds of changes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
/* Objects - master database */ SELECT oNew.* FROM [172.16.189.131].master.sys.all_objects oNew LEFT OUTER JOIN master.sys.all_objects oOld ON oNew.object_id = oOld.object_id AND oNew.is_ms_shipped = 1 WHERE oOld.name IS NULL AND oNew.is_ms_shipped = 1 ORDER BY oNew.type_desc, oNew.name; GO /* Objects - msdb database */ SELECT oNew.* FROM [172.16.189.131].msdb.sys.all_objects oNew LEFT OUTER JOIN msdb.sys.all_objects oOld ON oNew.name = oOld.name COLLATE DATABASE_DEFAULT AND oNew.is_ms_shipped = 1 WHERE oOld.name IS NULL AND oNew.is_ms_shipped = 1 AND oNew.type_desc NOT IN ('DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT', 'UNIQUE_CONSTRAINT') ORDER BY oNew.type_desc, oNew.name; GO /* New columns in existing objects - master */ SELECT oNew.type_desc, oNew.name, cNew.* FROM [172.16.189.131].master.sys.all_objects oNew INNER JOIN [172.16.189.131].master.sys.all_columns cNew ON oNew.object_id = cNew.object_id INNER JOIN master.sys.all_objects oOld ON oNew.object_id = oOld.object_id LEFT OUTER JOIN master.sys.all_columns cOld ON cNew.object_id = cOld.object_id AND cNew.column_id = cOld.column_id --AND cLocal.name = cRemote.name COLLATE DATABASE_DEFAULT WHERE cOld.name IS NULL AND oNew.is_ms_shipped = 1 ORDER BY oNew.type_desc, oNew.name, cNew.column_id; GO /* New columns in existing objects - msdb */ SELECT oNew.type_desc, oNew.name, cNew.* FROM [172.16.189.131].msdb.sys.all_objects oNew INNER JOIN [172.16.189.131].msdb.sys.all_columns cNew ON oNew.object_id = cNew.object_id INNER JOIN msdb.sys.all_objects oOld ON oNew.name = oOld.name COLLATE DATABASE_DEFAULT AND oNew.schema_id = oOld.schema_id LEFT OUTER JOIN msdb.sys.all_columns cOld ON oOld.object_id = cOld.object_id AND cNew.column_id = cOld.column_id WHERE cOld.name IS NULL AND oNew.is_ms_shipped = 1 ORDER BY oNew.type_desc, oNew.name, cNew.column_id; GO /* New messages */ SELECT mNew.*, '<li>' + CAST(mNew.message_id AS VARCHAR(10)) + ': ' + mNew.text + '</li>' AS text_html FROM [172.16.189.131].master.sys.messages mNew LEFT OUTER JOIN master.sys.messages mOld ON mNew.message_id = mOld.message_id AND mNew.language_id = mOld.language_id WHERE mOld.message_id IS NULL AND mNew.language_id = 1033 ORDER BY mNew.message_id; GO /* Configurations */ SELECT cNew.* FROM [172.16.189.131].master.sys.configurations cNew LEFT OUTER JOIN master.sys.configurations cOld ON cNew.configuration_id = cOld.configuration_id WHERE cOld.configuration_id IS NULL; /* Perfmon counters */ SELECT cNew.*, cOld.* FROM [172.16.189.131].master.sys.dm_os_performance_counters cNew LEFT OUTER JOIN master.sys.dm_os_performance_counters cOld ON SUBSTRING(TRIM(cNew.object_name),CHARINDEX(':',TRIM(cNew.object_name))+1,LEN(TRIM(cNew.object_name))) = SUBSTRING(TRIM(cOld.object_name),CHARINDEX(':',TRIM(cOld.object_name))+1,LEN(TRIM(cOld.object_name))) AND TRIM(cNew.counter_name) = TRIM(cOld.counter_name) -- AND TRIM(cNew.instance_name) = TRIM(cOld.instance_name) WHERE cOld.object_name IS NULL ORDER BY cNew.object_name, cNew.counter_name; |
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
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.