Emergency - procedure definition comparison across two instances

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];

Open in new window

LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor Commented:
I have changed my approach so that I am writing the procedure definition into the working table WITHOUT the servernames, using the nested REPLACE  below.  It DOES exclude the strings that I want to ignore from the body of the procedure that I am comparing.  BUT -- when I then compare the definition, looking for what does not match, it is returning objects that I cannot find any differences in.

Can anyone assist?

I should note, in this case there are 190 procedures in the database that I am comparing.  Supposed to be identical, minus the strings that I am ignoring.  My diff query returns 49.  I've looked at three of them, but I am not finding any objvious differences.  This is where I need an assist -- on the comparison.


-- this is what I use to load the procedure definition into a table from both instances, where one is 'uat' and the other is 'prod'
SELECT 'uat' [server_name],sc.[name] [schema_name],o.[name], o.[object_id], o.[type], 
REPLACE(
REPLACE(m.definition,'uat',''),
'servername.','') [definition]
FROM UAT.database.sys.objects o INNER JOIN UAT.database.sys.sql_modules m 
  ON o.object_id = m.object_id JOIN UAT.database.sys.schemas sc
    ON o.schema_id = sc.schema_id
WHERE o.[type] = 'p' 
AND o.name not like 'sp_%'
ORDER BY o.[Name];

Open in new window


-- this is what i use to then compare the definition, looking for the procedures on one server that do not match the same procedres on the other server
SELECT a.server_name,a.schema_name +'.'+ a.name [procedure],a.definition [prod],b.definition [uat],LEN(a.definition)
FROM dbo.proceduretable a JOIN dbo.proceduretable b
  ON a.schema_name +'.'+ a.name = b.schema_name +'.'+ b.name  
  AND a.type = b.type
WHERE a.type = 'P'
AND a.definition <> b.definition
AND a.server_name = 'prod'
AND b.server_name = 'uat'
ORDER BY server_name,[procedure];

Open in new window

Anthony PerkinsCommented:
Are you aware that there is a tool put out by Red-Gate called SQL Compare that does that already?

Having said that more than likely you have differences in white space or non-printable characters
dbaSQLAuthor Commented:
Yes, I am aware.  Unfortunately, as I said in the first post, 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.

I understand the white space could be a factor, but there has got to be a way to perform a reliable comparison of the object definition.   I understand, it may not be the easiest/best way, but I need something now.  This is why I am asking EE for the assistance.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Daniel_PLDB Expert/ArchitectCommented:
Hi,

In SQL Server two strings are just equal or not (no special flavors). However I'd like to recommend following link. You'll find there complete solution which allow you to more precisely compare your strings. You just have to use functions from the link with CROSS APPLY operator.

RedGate - Quantifying Text differences in TSQL

Regards,
Daniel
dbaSQLAuthor Commented:
I have setup SSDT Schema Compare.  Nice tool.  Easy to use, works very well -- but, they have explicit server and schema name references within the procedures that I am trying to ignore in the comparison.  New tool to me, but best I can see, there are no options to ignore strings in the comparison.  IF THERE ARE, PLEASE LET ME KNOW.  The only HUGE downside of the tool is that there is no way to print out the results, or output to xml or something, so that I can visualize the differences.  It is very tedious to walk through each returned item selectively, while in the gui.

If there are any SSDT Schema Compare Experts out there, I would love to hear if these options are available.  

And, I will continue to work on my tSQL version, because in my opinion, everything should be do-able via tSQL -- where relevant, of course.  On that note, if anyone can help me improve the construct I posted above -- or even this one -- cursor through the procs, excluding servername references, and outputing ONLY those procedures which have an actual variance -- that  would be incredible.



/* declarations */
DECLARE
	@procname VARCHAR(25) = 'procedure',
	@procdefinition NVARCHAR(4000),
	@hashedVal VARBINARY(4000) 

	SET @procdefinition = (SELECT OBJECT_DEFINITION (OBJECT_ID(@procname )))     
	SET @hashedVal = (select HashBytes('SHA1', @procdefinition)) 
	SELECT @hashedVal --<--- to use below

-------------------------------------------------------------------------------------
DECLARE 
	@procname VARCHAR(25) = 'procedure',
	@procdefinition NVARCHAR(4000),
	@hashedVal VARBINARY(4000),
	@hashedvalue VARBINARY(4000) --<-- using the above hashvalue

	SET @hashedvalue = 0x984E1923ACAE9E93C254E0EE72B244DDC5F056A7
	SET @procdefinition = (SELECT OBJECT_DEFINITION (OBJECT_ID(@procname )))    
        SET @hashedVal = (SELECT HashBytes('SHA1', @procdefinition))

	IF @hashedVal = @hashedvalue
    BEGIN
        SELECT 'no differences'
    END
	ELSE
		SELECT 'differences found'

	

Open in new window

dbaSQLAuthor Commented:
No need to keep this open any longer.  The SSDT tool helped me to some extent, and I don't need to pursue this any further at this time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
While you did not provide any specific assistance, I still appreciate that you took time to look.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.