Comparing stored procedures

Hi!
I've got 2 databases: working one and test one.
I've changed a lot of procedures in test database.
How can I compare all procedures in that databases and select only changed
ones?
Thanks !I recommend that you implement a change control system, where you know which has changed and apply
only those. To do an actual compare, you need to write code or go to 3:rd party product. I haven't
used below myself, but I see it mentioned a lot:
http://www.red-gate.com/SQL_Compare.htm
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I've got 2 databases: working one and test one.
> I've changed a lot of procedures in test database.
> How can I compare all procedures in that databases and select only changed
> ones?
> Thanks !
>|||For quick & dirty compare, use free tool QALite from site.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I've got 2 databases: working one and test one.
> I've changed a lot of procedures in test database.
> How can I compare all procedures in that databases and select only changed
> ones?
> Thanks !
>|||Hi, Dmitry
I always add some comment in my stored procedures like
--It has been changed by Uri Dimant on some date.
Write script which loop through all strored procedures and looking for some
comments.
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:#f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I've got 2 databases: working one and test one.
> I've changed a lot of procedures in test database.
> How can I compare all procedures in that databases and select only changed
> ones?
> Thanks !
>|||Hi Uri!
Could you post such script?
"Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> Hi, Dmitry
> I always add some comment in my stored procedures like
> --It has been changed by Uri Dimant on some date.
> Write script which loop through all strored procedures and looking for
some
> comments.|||Hi
Simple
SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.routines
WHERE ROUTINE_DEFINITION LIKE '%It has been changed%'
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:eXTqz9rnDHA.2776@.tk2msftngp13.phx.gbl...
> Hi Uri!
> Could you post such script?
> "Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
> news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> > Hi, Dmitry
> > I always add some comment in my stored procedures like
> > --It has been changed by Uri Dimant on some date.
> > Write script which loop through all strored procedures and looking for
> some
> > comments.
>|||You must to use the MS Visual Source Safe in a future to make a
version-control on of your procedures.
Hope this help,
"Dmitry Karneyev" <karneyev@.msn.com> escreveu na mensagem
news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I've got 2 databases: working one and test one.
> I've changed a lot of procedures in test database.
> How can I compare all procedures in that databases and select only changed
> ones?
> Thanks !
>|||That may not be robust! What if you forgot to put in a comment in one of the
stored procedures?
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxw0lCsnDHA.3304@.tk2msftngp13.phx.gbl...
> Hi
> Simple
> SELECT SPECIFIC_NAME
> FROM INFORMATION_SCHEMA.routines
> WHERE ROUTINE_DEFINITION LIKE '%It has been changed%'
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> news:eXTqz9rnDHA.2776@.tk2msftngp13.phx.gbl...
> > Hi Uri!
> >
> > Could you post such script?
> >
> > "Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
> > news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > Hi, Dmitry
> > > I always add some comment in my stored procedures like
> > > --It has been changed by Uri Dimant on some date.
> > > Write script which loop through all strored procedures and looking
for
> > some
> > > comments.
> >
> >
>|||Linchi
Heee, good point.
That will be DBA's privilege and only one person should to do such things
like me in our company.
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:#S6zLGunDHA.744@.tk2msftngp13.phx.gbl...
> That may not be robust! What if you forgot to put in a comment in one of
the
> stored procedures?
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxw0lCsnDHA.3304@.tk2msftngp13.phx.gbl...
> > Hi
> > Simple
> > SELECT SPECIFIC_NAME
> > FROM INFORMATION_SCHEMA.routines
> > WHERE ROUTINE_DEFINITION LIKE '%It has been changed%'
> >
> >
> > "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> > news:eXTqz9rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > Hi Uri!
> > >
> > > Could you post such script?
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
> > > news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > Hi, Dmitry
> > > > I always add some comment in my stored procedures like
> > > > --It has been changed by Uri Dimant on some date.
> > > > Write script which loop through all strored procedures and looking
> for
> > > some
> > > > comments.
> > >
> > >
> >
> >
>|||Thanks Linchi!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:O2DNNRunDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Here's a Perl script for comparing two stored procedures. You can use it
> compare any number of stored procedures. The first time it runs, it takes
> time to load SQL-DMO. An HTML doc is included in the zip file.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> > Hi!
> >
> > I've got 2 databases: working one and test one.
> > I've changed a lot of procedures in test database.
> >
> > How can I compare all procedures in that databases and select only
changed
> > ones?
> >
> > Thanks !
> >
> >
>
>|||Hi Linchi,
How to get the perl script you mentioned? I am having the
same problem and want to use your script to give a try.
Thanks in advance,
New Bee
>--Original Message--
>Here's a Perl script for comparing two stored procedures.
You can use it
>compare any number of stored procedures. The first time
it runs, it takes
>time to load SQL-DMO. An HTML doc is included in the zip
file.
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
>news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
>> Hi!
>> I've got 2 databases: working one and test one.
>> I've changed a lot of procedures in test database.
>> How can I compare all procedures in that databases and
select only changed
>> ones?
>> Thanks !
>>
>
>|||Please check DB Ghost at www.dbghost.com
Thanks,
Darren Fuller MCSE
>--Original Message--
>Hi!
>I've got 2 databases: working one and test one.
>I've changed a lot of procedures in test database.
>How can I compare all procedures in that databases and
select only changed
>ones?
>Thanks !
>
>.
>|||also routine_definition is not a reliable way to check for code.
e.g.
declare @.s1 varchar(8000),@.s2 varchar(8000),@.s3 varchar(8000)
select @.s1='create proc _usp as select ''',
@.s2=replicate('1',8000),
@.s3=replicate('2',8000)
exec(@.s1+@.s2+@.s3+'abc'' as mycol')
go
SELECT *
FROM INFORMATION_SCHEMA.routines
WHERE ROUTINE_DEFINITION LIKE '%abc'
select text
from syscomments
where id=object_id('_usp')
go
drop proc _usp
go
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxw0lCsnDHA.3304@.tk2msftngp13.phx.gbl...
> Hi
> Simple
> SELECT SPECIFIC_NAME
> FROM INFORMATION_SCHEMA.routines
> WHERE ROUTINE_DEFINITION LIKE '%It has been changed%'
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> news:eXTqz9rnDHA.2776@.tk2msftngp13.phx.gbl...
> > Hi Uri!
> >
> > Could you post such script?
> >
> > "Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
> > news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > Hi, Dmitry
> > > I always add some comment in my stored procedures like
> > > --It has been changed by Uri Dimant on some date.
> > > Write script which loop through all strored procedures and looking
for
> > some
> > > comments.
> >
> >
>|||NB;
I attached to my previous post. Are you not able to get the attached file? I can email you offline.
Also, the script requires the Perl module Parse::RecDescent which you can get from www.cpan.org. I use this module to parse T-SQL code to stripe off whitespaces and comments in case you don't want to compare them. You can choose to include them in the comparison if you want.
Linchi
>--Original Message--
>Thanks Linchi!
>"Linchi Shea" <linchi_shea@.NOSPAMml.com> ==D3=CF=CF=C2=DD=C9=CC/=D3=CF=CF=C2=DD=C9=CC=C1 =D7 =CE=CF=D7=CF=D3=D4=D1=C8
>=D3=CC=C5=C4=D5=C0=DD=C5=C5: =news:O2DNNRunDHA.2000@.TK2MSFTNGP12.phx.gbl...
>> Here's a Perl script for comparing two stored procedures. You can use it
>> compare any number of stored procedures. The first time it runs, it takes
>> time to load SQL-DMO. An HTML doc is included in the zip file.
>> -- >> Linchi Shea
>> linchi_shea@.NOSPAMml.com
>>
>> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
>> news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
>> > Hi!
>> >
>> > I've got 2 databases: working one and test one.
>> > I've changed a lot of procedures in test database.
>> >
>> > How can I compare all procedures in that databases and select only
>changed
>> > ones?
>> >
>> > Thanks !
>> >
>> >
>>
>
>.
>|||Even in a single DBA shop, it can end up biting you for DBAs can be
forgetful :-) In a multi-DBA shop, it's not going to work at all.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OQHYBLunDHA.688@.TK2MSFTNGP10.phx.gbl...
> Linchi
> Heee, good point.
> That will be DBA's privilege and only one person should to do such
things
> like me in our company.
>
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:#S6zLGunDHA.744@.tk2msftngp13.phx.gbl...
> > That may not be robust! What if you forgot to put in a comment in one of
> the
> > stored procedures?
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:uxw0lCsnDHA.3304@.tk2msftngp13.phx.gbl...
> > > Hi
> > > Simple
> > > SELECT SPECIFIC_NAME
> > > FROM INFORMATION_SCHEMA.routines
> > > WHERE ROUTINE_DEFINITION LIKE '%It has been changed%'
> > >
> > >
> > > "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> > > news:eXTqz9rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > Hi Uri!
> > > >
> > > > Could you post such script?
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ:
> > > > news:e02Zt6rnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > Hi, Dmitry
> > > > > I always add some comment in my stored procedures like
> > > > > --It has been changed by Uri Dimant on some date.
> > > > > Write script which loop through all strored procedures and
looking
> > for
> > > > some
> > > > > comments.
> > > >
> > > >
> > >
> > >
> >
> >
>|||NB;
Drop me an email!
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"NB" <anonymous@.discussions.microsoft.com> wrote in message
news:0a6601c39eef$06556d20$a501280a@.phx.gbl...
> Hi Linchi,
> How to get the perl script you mentioned? I am having the
> same problem and want to use your script to give a try.
> Thanks in advance,
> New Bee
> >--Original Message--
> >Here's a Perl script for comparing two stored procedures.
> You can use it
> >compare any number of stored procedures. The first time
> it runs, it takes
> >time to load SQL-DMO. An HTML doc is included in the zip
> file.
> >
> >--
> >Linchi Shea
> >linchi_shea@.NOSPAMml.com
> >
> >
> >"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> >news:%23f9gVqrnDHA.708@.TK2MSFTNGP10.phx.gbl...
> >> Hi!
> >>
> >> I've got 2 databases: working one and test one.
> >> I've changed a lot of procedures in test database.
> >>
> >> How can I compare all procedures in that databases and
> select only changed
> >> ones?
> >>
> >> Thanks !
> >>
> >>
> >
> >
> >