SQL Server Performance

Script all triggers in DB

Discussion in 'General Developer Questions' started by benwilson, Sep 13, 2005.

  1. benwilson New Member

    Hi All,
    I need to check all the triggers in our database to see if a particular value is referenced in them...is there any easy way to generate the scripts for all the triggers so i can just run a 'Find' against the scripts?

    Thanks,
    Ben

    'I reject your reality and substitute my own' - Adam Savage
    benwilson, Sep 13, 2005
    #2

  2. benwilson New Member

    ok, i had a bit of a play...here is what i came up with- seems to work:

    DECLARE @Name VARCHAR(300),
    @SQL VARCHAR(500)

    DECLARE BensCursor CURSOR FOR
    SELECT name
    FROM dbo.sysobjects
    WHERE (xtype = 'TR')

    OPEN BensCursor

    FETCH NEXT FROM BensCursor INTO @NAme

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'EXEC sp_helptext ' + @Name
    EXEC (@SQL)

    FETCH NEXT FROM BensCursor INTO @NAme
    END

    CLOSE BensCursor
    DEALLOCATE BensCursor

    Ben

    'I reject your reality and substitute my own' - Adam Savage
    benwilson, Sep 13, 2005
    #2

  3. dineshasanka Moderator

    dineshasanka, Sep 13, 2005
    #2

  4. benwilson New Member

    Thanks Dinesh...i had a look at that earlier, but didnt notice that it returns the definition <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Sure is a lot neater than my Cursor and dynamic SQL!<br /><br />'I reject your reality and substitute my own' - Adam Savage
    benwilson, Sep 13, 2005
    #2

  5. Madhivanan Moderator

    Why is the need of Cursor when Syscomments has this?

    Select object_name(id),text from syscomments where text like '%Create Trigger%'


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Sep 14, 2005
    #2

  6. dineshasanka Moderator

    Yes no need of cursors



    select
    "Table"=P.Name,
    "Trigger Name"=O.name,
    "Trigger Date"=O.refdate,
    "Defn"=CASE C.encrypted WHEN 0 THEN C.text ELSE '<< ENCRYPTED >>' END
    from
    syscomments C
    INNER JOIN sysobjects O ON (C.id = O.id)
    INNER JOIN sysobjects P ON (O.parent_obj = P.id)
    where
    O.xtype = 'TR' and
    P.xtype = 'U'


    also gettign data from yhe the syscomments table with some other information, so that you can filter other information as well



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
    dineshasanka, Sep 14, 2005
    #2

  7. Adriaan New Member

    Just make sure that you look at the length of the TEXT column, next you figure out what the COLID column is for.
    Adriaan, Sep 14, 2005
    #2

Share This Page