SQL Server Performance

Trigger definitions - not in information schema?

Discussion in 'General Developer Questions' started by geebee2, Mar 26, 2009.

  1. geebee2 New Member

    For SQL Functions and Procedures, I can retrieve definitions using
    select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES
    Is there any way to obtain Trigger definitions?
    geebee2, Mar 26, 2009
    #2

  2. Madhivanan Moderator

    select
    object_name(c.id) as trigger_name, text from syscomments as c inner join sysobjects as o on c.id=o.id where o.xtype='tr'
    Madhivanan, Mar 26, 2009
    #3

  3. Adriaan New Member

    Look out for definitions that take up multiple rows in syscomments - the "text" column is 4000 characters only.
    Adriaan, Mar 26, 2009
    #4

  4. geebee2 New Member

    "Look out for definitions that take up multiple rows in syscomments - the "text" column is 4000 characters only."
    Thanks - I ran into this, and just came back to post this caveat!
    Actually quite a pain, I will need to use a cursor to append to a text field or something.
    geebee2, Mar 26, 2009
    #5

  5. FrankKalis Moderator

    Btw, is this correct, that you're on SQL Server 2000?
    FrankKalis, Mar 27, 2009
    #6

  6. Adriaan New Member

    No, you can do a self-join on two instances of syscomments
    select object_name(com1.id), com1.text, com2.text
    from dbo.syscomments com1
    inner join dbo.syscomments com2 on com1.id = com2.id and (com1.colid = com2.colid - 1)
    where (com1.text + com2.text) like '%...................%'
    Adriaan, Mar 27, 2009
    #7

Share This Page