Home » Blog » Comparing System Objects between SQL Server 2012 and SQL Server 2008R2

Comparing System Objects between SQL Server 2012 and SQL Server 2008R2

2012-07-21 - General, SQL Server 2012

Introduction

Earlier today I was looking for a way to find changes between system objects in SQL Server 2008R2 and SQL Server 2012. To solve this quest, I spent a little bit of time to write a query to show all system objects that have changed. I am publishing the result of that exercise here for you to use.

The Query

The query looks at master.sys.all_objects in both instances using a linked server. It joins to sys.all_columns and to sys.all_parameteres to get the complete list of columns and parameters for each object. It also joins to sys.types to get that data type name for each column and each parameter. The column / parameter list is left joined to the object list, so objects without either will still be included. The query uses the is_ms_shipped column in sys.all_objects to include only objects that are part of the original SQL Server installation.

For each object schema, object name, column / parameter name and data type the query determines if it was found in 2012 or 2008R2 or both. That Information is returned in the first two columns. The query then uses window aggregates to see for each object, if it had a change or addition or drop of at least one column or parameter. If there was a change, all columns and parameters for that object will be included in the final result.

The final query can be found below.

[sql] SELECT
[2012],
[2008R2],
schema_name,
object_name,
ISNULL(column_name, parameter_name) [column / parameter],
type_desc object_type,
is_ms_shipped,
datatype_name,
max_length,
precision,
scale,
[2012 pos],
[2008R2 pos] FROM
(
SELECT
*,
COUNT(1) OVER(PARTITION BY schema_name, object_name) cp_count,
COUNT([2012]) OVER(PARTITION BY schema_name, object_name) cp12_count,
COUNT([2008R2]) OVER(PARTITION BY schema_name, object_name) cpr2_count
FROM
(
SELECT
CASE WHEN X12.object_name IS NOT NULL THEN '2012' END [2012],
CASE WHEN XR2.object_name IS NOT NULL THEN '2008R2' END [2008R2],
ISNULL(X12.schema_name,XR2.schema_name) schema_name,
ISNULL(X12.object_name,XR2.object_name) object_name,
ISNULL(X12.column_name,XR2.column_name) column_name,
ISNULL(X12.parameter_name,XR2.parameter_name) parameter_name,
ISNULL(X12.type_desc,XR2.type_desc) type_desc,
ISNULL(X12.is_ms_shipped,XR2.is_ms_shipped) is_ms_shipped,
ISNULL(X12.column_id, X12.parameter_id) [2012 pos],
ISNULL(XR2.column_id, XR2.parameter_id) [2008R2 pos],
ISNULL(X12.type_name,XR2.type_name) datatype_name,
ISNULL(X12.max_length,XR2.max_length) max_length,
ISNULL(X12.precision, XR2.precision) precision,
ISNULL(X12.scale, XR2.scale) scale,
ISNULL(1000000+ISNULL(x12.column_id,XR2.column_id),ISNULL(x12.parameter_id,XR2.parameter_id)) sort_order
FROM
(
SELECT s.name schema_name, o.name object_name, cp.column_name, cp.parameter_name, o.type_desc, o.is_ms_shipped,
cp.column_id, cp.parameter_id, cp.type_name, cp.max_length, cp.precision, cp.scale
FROM DEV_2012.master.sys.all_objects o
JOIN DEV_2012.master.sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN
(
SELECT ISNULL(c.object_id,p.object_id) object_id,c.name column_name, p.name parameter_name, c.column_id, p.parameter_id,
ISNULL(c.type_name,p.type_name) type_name, ISNULL(c.max_length,p.max_length) max_length,
ISNULL(c.precision, p.precision) precision, ISNULL(c.scale, p.scale) scale
FROM
(
SELECT c.object_id,c.column_id,c.name,t.name type_name,c.max_length,c.scale,c.precision
FROM DEV_2012.master.sys.all_columns c
JOIN DEV_2012.master.sys.types t
ON c.user_type_id = t.user_type_id
)c
FULL OUTER JOIN
(
SELECT p.object_id,p.parameter_id,p.name,t.name type_name,p.max_length,p.scale,p.precision
FROM DEV_2012.master.sys.all_parameters p
JOIN DEV_2012.master.sys.types t
ON p.user_type_id = t.user_type_id
)p
ON 1=2 -->UNION ALL
)cp
ON o.object_id = cp.object_id
)X12
FULL OUTER JOIN
(
SELECT s.name schema_name, o.name object_name, cp.column_name, cp.parameter_name, o.type_desc, o.is_ms_shipped,
cp.column_id, cp.parameter_id, cp.type_name, cp.max_length, cp.precision, cp.scale
FROM DEV_R2.master.sys.all_objects o
JOIN DEV_R2.master.sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN
(
SELECT ISNULL(c.object_id,p.object_id) object_id,c.name column_name, p.name parameter_name, c.column_id, p.parameter_id,
ISNULL(c.type_name,p.type_name) type_name, ISNULL(c.max_length,p.max_length) max_length,
ISNULL(c.precision, p.precision) precision, ISNULL(c.scale, p.scale) scale
FROM
(
SELECT c.object_id,c.column_id,c.name,t.name type_name,c.max_length,c.scale,c.precision
FROM DEV_R2.master.sys.all_columns c
JOIN DEV_R2.master.sys.types t
ON c.user_type_id = t.user_type_id
)c
FULL OUTER JOIN
(
SELECT p.object_id,p.parameter_id,p.name,t.name type_name,p.max_length,p.scale,p.precision
FROM DEV_R2.master.sys.all_parameters p
JOIN DEV_R2.master.sys.types t
ON p.user_type_id = t.user_type_id
)p
ON 1=2 -->UNION ALL
)cp
ON o.object_id = cp.object_id
)XR2
ON X12.object_name = XR2.object_name
AND X12.schema_name = XR2.schema_name
AND ISNULL(X12.column_name,'') = ISNULL(XR2.column_name,'')
AND ISNULL(X12.parameter_name,'') = ISNULL(XR2.parameter_name,'')
AND ISNULL(X12.type_name,'') = ISNULL(XR2.type_name,'')
AND ISNULL(X12.max_length,0) = ISNULL(XR2.max_length,0)
AND ISNULL(X12.precision,0) = ISNULL(XR2.precision,0)
AND ISNULL(X12.scale,0) = ISNULL(XR2.scale,0)
WHERE 1=1
--AND (X12.object_name IS NULL OR XR2.object_name IS NULL)
AND ISNULL(X12.is_ms_shipped,XR2.is_ms_shipped) = 1
)X
WHERE X.type_desc != 'SYSTEM_TABLE'
)X
WHERE cp_count != cp12_count OR cp_count != cpr2_count
ORDER BY type_desc, object_name, schema_name, sort_order;
[/sql]

The query executes on 2012 and 2008R2. It uses four-part names to access the master database of a 2008R2 and a 2012 instance. To run the query in your system you have to setup appropriate linked servers. You can use the following code as a starting point for that:

[sql] EXEC master.dbo.sp_addlinkedserver @server = N'DEV_R2', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEV_R2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
[/sql]

@useself=N'true' means that the current login information is used to try to connect to that linked server.

Conclusion

There are several exciting changes in the system objects in 2012. They range from more descriptive column names to entirely new DMVs. Have fun running your own investigation.