Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
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, '')
Version Information
- Entered : 2/1/2012
You could leave a comment if you were logged in.