February 9, 2009 at 1:33 pm
Comments posted to this topic are about the item Comparing Stored Procedures, Part 1
March 10, 2009 at 11:04 am
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.
March 10, 2009 at 11:22 am
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: http://www.sqlservercentral.com/scripts/TSQL/66074/. You can also check out the blog entry here: http://jessesql.blogspot.com/2009/02/comparing-stored-procedures-part-6.html, 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:
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 S1
FULL OUTER JOIN Seq2 S2
ON S1.CodeLineNum = S2.MatchLineNum
ORDER BY OrderBy
March 18, 2009 at 4:13 am
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.
April 28, 2009 at 12:08 pm
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.
May 26, 2009 at 10:03 am
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.
September 17, 2009 at 3:02 pm
I have script that will compares 2 objects. It produce result similar to windiff. You don't need to export object
posted in scripts http://www.sqlservercentral.com/scripts/Maintenance/64277/
October 1, 2009 at 9:56 am
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.
Jason
http://dbace.us
😛
December 11, 2009 at 10:09 am
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)
May 17, 2016 at 6:44 am
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply