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
  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
  3. dineshasanka Moderator

  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
  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
  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
  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.

Share This Page