SQL Server Performance

How to find trigger on table????

Discussion in 'General Developer Questions' started by ranjitjain, Apr 20, 2005.

  1. ranjitjain New Member

    Hi Experts,
    How can i find tables which has trigger associated with it?? without clicking
    each table design properties using EM.
    ranjitjain, Apr 20, 2005
    #2

  2. dineshasanka Moderator

    select name,OBJECT_NAME(parent_obj) from sysobjects where xtype = 'TR'

    quote:Originally posted by ranjitjain

    Hi Experts,
    How can i find tables which has trigger associated with it?? without clicking
    each table design properties using EM.

    dineshasanka, Apr 20, 2005
    #2

  3. ranjitjain New Member

    Thanx Dinesh for increasing my knowledgebase.
    ranjitjain, Apr 21, 2005
    #2

  4. dineshasanka Moderator

    Welcome and thankx for refreshing my knowledge!

    quote:Originally posted by ranjitjain

    Thanx Dinesh for increasing my knowledgebase.

    dineshasanka, Apr 21, 2005
    #2

  5. dineshasanka Moderator

    SP_HELPTRIGGER will also give you the same result

    sp_helptrigger
    Returns the type or types of triggers defined on the specified table for the current database.

    Syntax
    sp_helptrigger [ @tabname = ] 'table'
    [ , [ @triggertype = ] 'type' ]
    dineshasanka, Apr 21, 2005
    #2

  6. satya Moderator

    I would encourage to use pre-supplied stored procedures rather than going directly system tables, this is one of the good practice. If not use INFORMATION.SCHEMA Views to accompish the task.

    BOL is your friend.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Apr 21, 2005
    #2

  7. ranjitjain New Member

    okies sire ill always follow the pre supplied procedures.
    But for the sack of knowledge ill keep the system table select query too in my mind
    Thanx a lot
    ranjitjain, Apr 21, 2005
    #2

  8. satya Moderator

    Take a look at Books online for system stored procedures, information schema views and others, which gives you indepth of solution for the requirement.

    There is nothing wrong in using system tables, but in the future versions MS may change the fashion of the tables and you may hit the problem when using the query which uses system tables.

    As they say.....prevention is better than cure....

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Apr 21, 2005
    #2

  9. Fusion007 New Member

    By using this query select name from sys.Triggers. you will get the list of triggers in the database.
    by using sp_depends "triggername". you will get the table which the given trigger has been written.

    or use this query to get list of triggers and associated tables:

    select name, object_name(parent_id) from sys.triggers
    Fusion007, Jun 22, 2011
    #9

Share This Page