August 11, 2009 | SQL Server

New / changed columns in SQL Server 2008 R2 DMVs

I was expecting more changes, but as Denis Gobo already pointed out, there is not a single new DMV in SQL Server 2008 R2.  There have been some column additions, however, as well as a couple that have increased in size.  You might think that I determined this by loading both Books Online collections into some kind of amazing diff tool, or spent hours poring over the sets of DMV definitions.  No, there is a much easier way.  I installed an instance of 2008, then an instance of R2, and then created a linked server on the 2008 instance to the R2 instance ("SQL2008R2").  Then it was a fairly simple query:

SELECT 
   [status] = CASE 
       WHEN l.viewname IS NULL THEN 'new' 
       ELSE 'changed' 
   END,
   r.viewname,
   r.col,
   r.[type],
   FormerT = l.[type],
   r.[precision],
   FormerP = l.[precision],
   r.scale,
   FormerS = l.scale,
   r.max_length,
   FormerML = l.max_length
FROM
(
   SELECT
       viewname = v.name,
       col = c.name,
       t = c.system_type_id,
       [type] = t.name,
       c.[precision],
       c.scale,
       c.max_length 
   FROM
       [SQL2008R2].[master].sys.all_columns AS c
   INNER JOIN
       [SQL2008R2].[master].sys.all_views AS v
       ON c.[object_id] = v.[object_id]
   INNER JOIN
       [SQL2008R2].[master].sys.types AS t
       ON t.system_type_id = c.system_type_id
   WHERE
       v.name LIKE 'dm[_]%'
       AND t.name <> 'sysname'
) AS r
LEFT OUTER JOIN
(
   SELECT
       viewname = v.name,
       col = c.name,
       t = c.system_type_id,
       [type] = t.name,
       c.[precision],
       c.scale,
       c.max_length
   FROM
       [master].sys.all_columns AS c
   INNER JOIN
       [master].sys.all_views AS v
       ON c.[object_id] = v.[object_id]
   INNER JOIN
       [master].sys.types AS t
       ON t.system_type_id = c.system_type_id
   WHERE
       v.name LIKE 'dm[_]%'
       AND t.name <> 'sysname'
) AS l
ON 
   r.viewname = l.viewname
   AND r.col = l.col
WHERE
   l.viewname IS NULL
   OR r.t <> COALESCE(l.t, -10)
   OR r.[precision] <> COALESCE(l.[precision], -10)
   OR r.scale <> COALESCE(l.scale, -10)
   OR r.max_length <> COALESCE(l.max_length, -10)
ORDER BY
   [status] DESC, 
   r.viewname,
   r.col;

The results:

So, 10 new columns, all in the sys.dm_os_* DMVs.  For a couple of other DMVs, one column got a little bigger.

1 comment on this post

    • Jungsun - August 18, 2009, 6:21 PM

      Thanks, Aaron

Comments are closed.