Compare SP 's content ?

Besides buy a third-party program, any way to compare the content of the SP
in two different SQL server '
Thanks a lotHi,
this will get you the Procedure text, comparing should be easy for you now,
if you need further help just raise a hand.
Select sc.* from syscomments sc
inner join sysobjects so ON
so.id = sc.id
Where XType = 'P' AND
so.name like 'YourProcedurename'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Besides buy a third-party program, any way to compare the content of the
> SP in two different SQL server '
> Thanks a lot
>|||The way I do it is to generate a SQL script from each, and then just use 'fc
'
or 'diff'. I'm sure there are better ways of doing it though...|||Agnes
Try this one
if (select checksum_agg(checksum(ROUTINE_DEFINITION
))FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc')
<> (select checksum_agg(checksum(ROUTINE_DEFINITION
))FROM
DatabaseName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc')
print 'different'
else
print 'probably the same'
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Besides buy a third-party program, any way to compare the content of the
SP
> in two different SQL server '
> Thanks a lot
>|||Jens
I'd avoid querying system tables inseatd use
SELECT ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OL5ifkGVFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> this will get you the Procedure text, comparing should be easy for you
now,
> if you need further help just raise a hand.
> Select sc.* from syscomments sc
> inner join sysobjects so ON
> so.id = sc.id
> Where XType = 'P' AND
> so.name like 'YourProcedurename'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||Unless you have a good algorithmn, comparing SP's as already indicated will
not yield consistent results.
The code of an SP can be functionally identical but fail textual, checksum,
or line by line text / checksum comparisons.
At the end ofthe day it is pointless - SP's are easily recreated, so after
the schema is verified, just recompile all of the a-new.
I do have a tool that does this (and much more) and works reliably possibly
for the same reasons as you, but it is not a released product.
If you are interested in beta testing the product, post back with a munged
email while I think about it :)
- Tim
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OL5ifkGVFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> this will get you the Procedure text, comparing should be easy for you
> now, if you need further help just raise a hand.
> Select sc.* from syscomments sc
> inner join sysobjects so ON
> so.id = sc.id
> Where XType = 'P' AND
> so.name like 'YourProcedurename'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||edit both, save to text file and use windiff ?|||If you put a blank line or extra space in any of the sp's these methods will
fail.
If there are any structured comments with revision details in, again, these
methods will fail.
If the objective is to ensure that the SP's are not tampered with then a
checksum system with fc is a good starter. However a better starting point
would be security and traces.
"Mark" <nospam> wrote in message
news:%23gtuPJHVFHA.1404@.TK2MSFTNGP09.phx.gbl...
> edit both, save to text file and use windiff ?
>