Post reply

finding out which stored procedures been modified in other database...

  • July 13, 2004 at 7:15 pm

    #85851

    Hi GURUs! Is there a way that you can find out all the stored procedure been modify in one database in SQL SERVER 2000? so that I know which stored procedure need to update in the other database, know what I mean?

    Thanks in advance!

  • July 13, 2004 at 10:34 pm

    #514495

    I think you are heading towards some sort of SQL differencing tool - ie something that allows you to compare two SQL Server dbs and identify schema differences between the them.

    There are several available - I have used AdeptSQL Diff in the past (http://www.adeptsql.com/) and found it to work well - if you want, it even creates the SQL for you to perform the changes - in either direction.

    Regards

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 13, 2004 at 10:55 pm

    #514499

    Thanks Phil...

    I just need to know that the stored procedures had been modified by someone and need also to update in other database...

    Third-party tools is the only solutions?

  • July 14, 2004 at 12:11 am

    #514504

    Thats correct. I don't believe SQL Server records that sort of information. SQL Server 2005 may do though!

    Kindest Regards,

  • July 15, 2004 at 12:38 am

    #514592

    hello all,

    you may want to look at http://www.dbghost.com as well which the only true database change management solution for SQL Server available on the market today. Go to the site and read the white paper (a pdf download) as it makes for very interesting reading. Every shop should have this.

    Making the change is not change management, knowing who, when and a complete history of the database object in question as well as the ability to see what has changed and make those changes becomes change management.

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    Living and breathing database change management for SQL Server

     

     

  • July 15, 2004 at 7:26 am

    #514644

    There is an Open Source Project: http://www.davidemauri.it/dabcos/

     

     

  • July 15, 2004 at 10:31 am

    #514698

    Thanks Ignacio...I will check this link....

  • July 15, 2004 at 11:18 am

    #514706

    For a rough solution, you can use a CHECKSUM approach. Below are links to an article, a script, and comments on SQLServerCentral that go me going on this.

    http://qa.sqlservercentral.com/columnists/jgama/tsqlvirusorbomb.asp

    http://qa.sqlservercentral.com/scripts/contributions/630.asp

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=100&messageid=18921#bm90143



    Everett Wilson
    ewilson10@yahoo.com

  • July 15, 2004 at 11:44 am

    #514713

    If the procs are only changed by dropping/creating then the following sql will show you when they were last created:

    select name, crdate from sysobjects where xtype = 'p' order by crdate desc

    This doesn't show if the procs were just edited. You need to run:

    select name,crdate,text from sysobjects o left join syscomments c on o.id = c.id where xtype = 'p'

    to output the procedure text from the two databases, then compare them using some comparing tool. If the 'changed' database is accessible from the database to be changed, you could easily use the sql below to compare the contents of the syscomments.text column from the two databases:

    select o.name,o.crdate,c.text, c.colid, a.name, a.crdate, a.othertext, a.colid2 from mortgagepro.dbo.sysobjects o left join mortgagepro.dbo.syscomments c on o.id = c.id

    left join (select name,crdate,text as othertext, colid as colid2 from mortgagepro_archive.dbo.sysobjects o left join mortgagepro_archive.dbo.syscomments c on o.id = c.id where xtype = 'p') a

    on o.name = a.name and c.colid = a.colid2

    where xtype = 'p' and c.text <> a.othertext

    where mortgagepro and mortgagepro_archive are the two databases in this example.

    Hope this helps

    Peter

  • July 15, 2004 at 12:37 pm

    #514729

    Thanks for the all info guys!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply