SQLTeam.com | Weblogs | Forums

How to compare the two Stored Procedure


#1

Hi,

There are two stored procedures which has same name and same logic. But in different databases.
Is there any way to compare code between above two stored procedures?
Could you please help on this .

DB 1 - SOMC
SP Name - Copylist.

DB 2 - VCMC
SP Name - Copylist.


#2

script out the stored procedure to text file and then use text editor to compare


#3

There are third party tools that perform this type of comparison. We use RedGate Compare and find it useful but there are other vendors who have a tool that does the same basic thing.


#4

There are many tools present in the market, you can take help of them.
You can identify which procedures (and other objects with slight modification) are different using the script below.

select S1.name [Db1_Schema], O1.name as [Db1_Object], O1.modify_date,
S2.name [Db1_Schema], O2.name as [Db1_Object], O2.modify_date
from database.sys.all_objects O1
inner join database2.sys.all_objects O2 on O1.name = O2.name
inner join database.sys.syscomments C1 on O1.object_id = C1.id
inner join database2.sys.syscomments C2 on O2.object_id = C2.id
inner join database.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join database2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P'

#5

use dbForge Schema Compare for SQL Server
for compare all objects of tow databases
google it ,download it and use it


#6

Try this out
https://social.msdn.microsoft.com/Forums/en-US/9a23a5ec-f79b-4c18-b4f6-e52b54de71eb/how-to-compare-sql-server-stored-procedures-from-two-separate-databases?forum=sqldatabaseengine