Where can I find all the triggers in my database
Results 1 to 5 of 5

Thread: Where can I find all the triggers in my database

  1. #1
    Ali Alhussein Guest

    Where can I find all the triggers in my database


    Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

    Thanks

    Ali

  2. #2
    axel Guest

    Where can I find all the triggers in my database (reply)

    Hi,

    In sysobjects you can find most of all objects
    So if youwant o see for example the triggers on the statstables table try this query !!!

    select otabl.name , otr.name
    from sysobjects as otabl
    inner join sysobjects as otr on otabl.id = otr.parent_obj
    where otr.type = 'tr' and otabl.name = 'statstables'

    I think it can help you

    Best regards

    Axel
    ------------
    Ali Alhussein at 3/21/01 9:20:21 AM


    Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

    Thanks

    Ali

  3. #3
    Larry Guest

    Where can I find all the triggers in my database (reply)

    Ali, there's a couple of ways (that I know) to do this:

    1) Query the system tables (not recommended by M$ though) like this:

    select so.name, so2.name as [table]
    from sysobjects so
    join sysobjects so2 on so.parent_obj = so2.id
    and so.xtype = 'tr'

    2) Execute 'sp_helptrigger' for each table in the current database:

    sp_msforeachtable "print '?' exec sp_helptrigger '?'"

    Hope this helps.

    - Larry

    ------------
    Ali Alhussein at 3/21/01 9:20:21 AM


    Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

    Thanks

    Ali

  4. #4
    Ali Alhussein Guest

    Thanks


    Thanks for your

    ------------
    axel at 3/21/01 10:59:38 AM

    Hi,

    In sysobjects you can find most of all objects
    So if youwant o see for example the triggers on the statstables table try this query !!!

    select otabl.name , otr.name
    from sysobjects as otabl
    inner join sysobjects as otr on otabl.id = otr.parent_obj
    where otr.type = 'tr' and otabl.name = 'statstables'

    I think it can help you

    Best regards

    Axel
    ------------
    Ali Alhussein at 3/21/01 9:20:21 AM


    Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

    Thanks

    Ali

  5. #5
    Jonathan Yang Guest

    not documented in SQL 2000 OL book; Larry how do you know this?

    It works! But sp_msforeachtable is not documented in SQL 2000 OL book.

    Larry how do you know this?


    ------------
    Larry at 3/21/01 11:00:41 AM

    Ali, there's a couple of ways (that I know) to do this:

    1) Query the system tables (not recommended by M$ though) like this:

    select so.name, so2.name as [table]
    from sysobjects so
    join sysobjects so2 on so.parent_obj = so2.id
    and so.xtype = 'tr'

    2) Execute 'sp_helptrigger' for each table in the current database:

    sp_msforeachtable "print '?' exec sp_helptrigger '?'"

    Hope this helps.

    - Larry

    ------------
    Ali Alhussein at 3/21/01 9:20:21 AM


    Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

    Thanks

    Ali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •