I have an emergency need to compare object definition across a few servers -- procedures, tables and views. I've got all object definition loaded into a bunch of different tables, but I have one problem.
The production objects are from a 'PROD' server, and there are explicit references to 'PROD'.
The uat objects are from a 'UAT' server, and there are explicit references to 'UAT'.
When I compare he object definition, I want to exclude the server name references, such that I am only comparing the rest of the procedure definition. I've written the piece below, thinking my REPLACE would do it --- but it does not appear to be. I say that because I looked at one of the variances it returned, and I do not see any differences aside from the server names.
A third party tool is not an option. I need to compare the objects across two servers ASAP, and identify which ones are different. It is SQL Server v2014.
SELECT a.server_name,a.schema_name +'.'+ a.name [procedure],a.definition [prod],b.definition [uat]
FROM dbo.Procedures a JOIN dbo.Procedures b
ON a.schema_name +'.'+ a.name = b.schema_name +'.'+ b.name
AND a.type = b.type
WHERE a.type = 'P'
AND REPLACE(a.definition,'PROD','') <> REPLACE(b.definition,'UAT','')
AND a.server_name = 'PROD'
AND b.server_name = 'UAT'
ORDER BY server_name,[procedure];