Comparing Stored Procedures SQL Server 2000

Have you been frustrated by a bad release ?? trying to figure out where the problem is?? well I've been, and I had to verify that my release team did the job correctly, principally with the stored procedures, it is too dificult to mantain up to date and find differences, this is my first draft(even it's working) about a stored procedure comparer, my initial idea is to eliminate all the special characters and compare row by row and find differences, in my second review (if i had time) I'll try to add the line or the source code that is different:


select distinct B.name Local,
A.name Remote,
case when replace(replace(replace(replace(A.text,' ',''),char(13),''),char(10),''),char(9),'') =
replace(replace(replace(replace(C.text,' ',''),char(13),''),char(10),''),char(9),'') then
'Equal'
else
'Not Equal'
end Equal
from OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=serverName;UID=userName;PWD=secretpwd;DATABASE=myDB',
'select A.name, B.text, B.colid
from outbound..sysobjects A
inner join outbound..syscomments B on A.id = B.id
where A.name like ''pr_Prefix_%'' and A.type=''P'' ') A
left join outbound..sysobjects B on A.name = B.name
left join outbound..syscomments C on B.id = C.id
and C.colid = A.colid


All that you have to do is replace the serverName, userName, secretpwd and myDB, also if you want to find differences between a group of stored procedures that the name has a prefix, then replace pr_Prefix_ with your naming convention, and that's it!!

Let me know your comments or improvements ;)

Tr8's life

This is my first post on this, thanks to Larin (http://chischaschos.blogspot.com) well it's not my first post on globspot, I mean on this account :p. This is my profile:

Bachelor Degree on Computer science (But I'm not a geek)
Beer drinker
Fishing man
Married with two devils
Working in a transnational software company as Software and Analyst Engineer
Travian player (yes I know that this is a bad habit)

well, my brain is not working propertly I'm trying to finish a report, a very large report
. cya!