SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jesse McLain / Comparing Stored Procedures, Part 1 / Latest Posts

RE: Comparing Stored Procedures, Part 1

Pratap Prabhu — Fri, 11 Dec 2009 10:09:36 GMT

Hi Back in 2008 I posted a similar script 'Find Mismatched Views and Stored Procs' (http://www.sqlservercentral.com/scripts/Development/63270/), given 2 databases it lists out the mismatched Views and stored Procs. The Extreme right columns are the stored procs/views scripts. Check it out, maybe it could be some help. (Note:If the databases are on different physical servers just create a linked database)

RE: Comparing Stored Procedures, Part 1

jswong05 — Thu, 01 Oct 2009 09:56:16 GMT

Thanks for the effort. Since SPs are just ASCII text, a:w00t:ny diff program will do. diff.exe comes from windows free resource kit download. Any programmers should be able to diff.

RE: Comparing Stored Procedures, Part 1

Larissa-136281 — Thu, 17 Sep 2009 15:02:55 GMT

I have script that will compares 2 objects. It produce result similar to windiff. You don't need to export objectposted in scripts http://www.sqlservercentral.com/scripts/Maintenance/64277/

RE: Comparing Stored Procedures, Part 1

Pratap Prabhu — Tue, 26 May 2009 10:03:36 GMT

I myself prefer using Visual Source Safe. I use this for my StoredProcs on my dev box. For mismatches between the DevBox and the Staging servers I use a stored proc I developed a few years ago ( sp_utl_FindMisMatchedObjects published here on SQLServerCentral http://www.sqlservercentral.com/scripts/Development/63270/ ) The stored proc can also be used against a Production server, but in my setup the production servers are not directly accessible from the Staging and Dev servers.

RE: Comparing Stored Procedures, Part 1

tim-707944 — Tue, 28 Apr 2009 12:08:27 GMT

This solution works great as long as the database servers are both accessible. In our shop, our Dev, Test, and Production systems are walled off from each other, so there is no reliable way to do that sort of querying.In our case, I typically just script them out and use BeyondCompare to find differances.

RE: Comparing Stored Procedures, Part 1

P Jones — Wed, 18 Mar 2009 04:13:29 GMT

I just script my stored procs off the two servers to text file and use WinDiff to compare side by side. No cost. No hassle.

RE: Comparing Stored Procedures, Part 1

Jesse McLain — Tue, 10 Mar 2009 11:22:29 GMT

UltraEdit is a great product, I used to use it extensively in the past. I don't think it had the 'compare files' feature back then.This effort to compare stored procedures has gone through several iterations, the most recent of which uses a recursive CTE: [url]http://www.sqlservercentral.com/scripts/TSQL/66074/[/url]. You can also check out the blog entry here: [url]http://jessesql.blogspot.com/2009/02/comparing-stored-procedures-part-6.html[/url], which explains a lot of my thought process, and demonstrates some performance charting.Using the most recent version above, you can run this query below on the results to achieve a visual comparison:[code]SELECT Seq1_Line = ISNULL(LTRIM(STR(S1.CodeLineNum)), ''),ISNULL(S1.CodeLineTxt, ''),ISNULL(S2.CodeLineTxt, ''),Seq2_Line = ISNULL(LTRIM(STR(S2.CodeLineNum)), ''),OrderBy = ISNULL(S1.CodeLineNum, S2.CodeLineNum)FROM Seq1 S1FULL OUTER JOIN Seq2 S2ON S1.CodeLineNum = S2.MatchLineNumORDER BY OrderBy[/code]

RE: Comparing Stored Procedures, Part 1

mstjean — Tue, 10 Mar 2009 11:04:33 GMT

You may want to check out Ultra Edit. Save 2 usp's to text files and do File/Compare Files. It shows the files side by side with the extra/missing rows highlighted.It also does column mode editing which comes in handy.

Comparing Stored Procedures, Part 1

Jesse McLain — Mon, 09 Feb 2009 13:33:57 GMT

Comments posted to this topic are about the item [B]Comparing Stored Procedures, Part 1[/B]