Comments posted to this topic are about the item Comparing Stored Procedures, Part 1
Hall of Fame
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.
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:
Seq1_Line = ISNULL(LTRIM(STR(S1.CodeLineNum)), '')
,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
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.
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.
Say Hey Kid
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.
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/
Hall of Fame
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.
Say Hey Kid
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)
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)