This SQL will compare the Part UDF values of two databases attached to the same SQL server. The purpose of this query is to identify if there are any differences between the two databases and what those changes are specifically. This is helpful if you are switching from one version to another and need a summarized list of changes between the two.

None. This is a selection query and no data is modified in the running of it.

SELECT (SELECT ItemName FROM Part WHERE ID = COALESCE(NewUDFs.PartID, OldUDFs.PartID)) AS Partname, *
FROM
(
	SELECT * 
	FROM dbo.GetAllPartUDFs()
	WHERE PartID IN (SELECT P.ID FROM part p
					 LEFT JOIN system_oobmasterv269.dbo.part op ON op.id = p.id
					  WHERE CAST(P.UDFXML AS VARCHAR(MAX)) CAST(OP.UDFXML AS VARCHAR(MAX)))
) NewUDFs
FULL OUTER JOIN
(
	SELECT * 
	FROM system_oobmasterv269.dbo.GetAllPartUDFs()
	WHERE PartID IN (SELECT P.ID FROM part p
					 LEFT JOIN system_oobmasterv269.dbo.part op ON op.id = p.id
					  WHERE CAST(P.UDFXML AS VARCHAR(MAX)) CAST(OP.UDFXML AS VARCHAR(MAX)))
) OldUDFs
ON NewUDFs.PartID = OldUDFs.PartID AND CAST(NewUDFs.VariableName AS VARCHAR(100)) = CAST(OldUDFs.VariableName AS VARCHAR(100))
WHERE COALESCE(NewUDFs.UDFValue, '')  COALESCE(OldUDFs.UDFValue, '')
  • Entered : 2/1/2012
You could leave a comment if you were logged in.