Compare procedure Definition within two databases

We have an instance of Sql Server 2012, In the Instance we have 2 dbs called A and B ( Assume A as production DB and B as Development DB and B is the exact copy of Database A )

suppose For Development Purpose,Some procedure ( Already Existed,Not Newly Created ) definition were changed in database B but not in A ( Production database ).

In order to Find the Procedures that are different by Definition i have came up with the following scenario,But the Problem is i not 100% Sure that this scenario works every time. So please Suggest Your ideas On this.

Scenario

Temporary Table 1 ( A_PROCEDURE ) — To Hold all the procedure name and Definition exists in database A ( Executed from Database A )

select * into A_PROCEDURE FROM (
Select routine_name,routine_definition from INFORMATION_SCHEMA.ROUTINES ) DTL;

Now From Database B i have Executed following Query To find All the Procedure Which are Not same by the Procedure Definition

SELECT T.ROUTINE_NAME B_PROC,
L.ROUTINE_NAME A_PROC,T.ROUTINE_DEFINITION B_PROC_DEF,L.ROUTINE_DEFINITION A_PROC_DEF
FROM INFORMATION_SCHEMA.ROUTINES T JOIN A.DBO.A_PROCEDURE L ON T.ROUTINE_NAME = L.ROUTINE_NAME
AND T.ROUTINE_DEFINITION <> L.ROUTINE_DEFINITION;

From the above Query i have found Procedures which are same by name But Not same by definition ( Which are Correct -manually Checked Line By line ).

So is this a proper way ? Should Consider anything else ? Please Share your ideas on this

Thanks and Best Regards, Vishnudas

Answer

Hi Vishnudas: You need sp_HelpText system stored procedure. This utility stores the definition of every stored procedure in a SQL Server database engine.

It’s worth for every DBA or developer to use a comparison tool. In the meanwhile, you can check this blog where you could write a script without stats about the in-line comparison of two stored procedures. There, you get an explanation about the use of sp_HelpText.

However, I suggest to use a comparison tool like SQL Compare tool, used by production teams in some middle and large companies. An useful top list is recommended and it was reviewed by another peers.