Post reply

changes objects ?

  • December 28, 2007 at 8:05 am

    #120063

    Can someone help me with this :

    I'd like to know how i can find every stored procedure/trigger .. table which has been changes since a certain date.

    How can I do this ?

    For the moment I'm browsing through the tree view in the Management Studio but i guess there must be a better way

    Thanks

  • December 28, 2007 at 8:11 am

    #763284

    select * from sys.procedures where modify_date > @Date

    select * from sys.triggers where modify_date > @date

    Or, if you want to check all objects

    select * from sys.objects where modify_date > @date

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 28, 2007 at 12:27 pm

    #763381

    Thank You, This was something i was looking for.

    Is there also a way to export the found objects in a script to a text file so I can import them

    on another server ?

  • December 28, 2007 at 1:29 pm

    #763408

    Easiest way is to use a cursor. For each item found, call sp_helptext.

    Set the output in management studio to text, then you can save the whole lot as a file

    SET NOCOUNT ON

    DECLARE @ObjectName SYSNAME

    DECLARE curRecentItems CURSOR LOCAL FAST_FORWARD FOR

    SELECT name FROM sys.procedures WHERE modify_date > @SomeDate

    OPEN curRecentItems

    FETCH NEXT FROM curRecentItems INTO @ObjectName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC sp_helpText @ObjectName

    FETCH NEXT FROM curRecentItems INTO @ObjectName

    END

    CLOSE curRecentItems

    DEALLOCATE curRecentItems

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 28, 2007 at 2:15 pm

    #763432

    Great solution Gila 🙂

    Bart,

    Do not forget to remove column headers in the results to text; otherwise, you will have the word "text" before each stored procedure.


    My blog: http://jahaines.blogspot.com

  • January 9, 2008 at 6:10 am

    #766119

    Hi,

    how do you remove the default column (text) and underlines (----------------)?

    Thanks

    kj

  • January 9, 2008 at 6:15 am

    #766120

    Easiest thing is just search and replace before you save the script

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

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