Thursday, November 8, 2007

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 Local, 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
'Not Equal'
end Equal
'DRIVER={SQL Server};SERVER=serverName;UID=userName;PWD=secretpwd;DATABASE=myDB',
'select, B.text, B.colid
from outbound..sysobjects A
inner join outbound..syscomments B on =
where like ''pr_Prefix_%'' and A.type=''P'' ') A
left join outbound..sysobjects B on =
left join outbound..syscomments C on =
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 ;)

1 comment:

Hk said...

hey dude, don't suck, why do you write those stupid things abut SQL Server, c'mon...

just joking, but I think you shoud changer your entries...

regards mr. tr8