List Out All User Defined Triggers

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Comments posted to this topic are about the item List Out All User Defined Triggers

    Thanks.

  • Sporidium

    Default port

    Points: 1460

    Hi Sourav

    Thanks for contributing.

    Just two minor points:

    1. The script as it stands lists disabled triggers rather than all triggers - to be fair this was probably your intention and it just got mis-titled.

    2. Be aware that using syscomments to return the trigger text is prone to truncation after 8000 characters - it's better to use the OBJECT_DEFINITION() function, not to mention that use of legacy system table such as syscomments and sysdatabases is deprecated.

    Thanks again and sorry for being picky

    Gary

  • Lee Linares

    SSCrazy

    Points: 2693

    Thanks for taking the time share your script.

    Expanding on Gary's points you could eliminate all the JOINS by using something like this:

    'SELECT

    db_name() as Database_Name,

    name as Trigger_Name,

    OBJECT_NAME(parent_id) as Associated_Table_Name,

    is_ms_shipped,

    OBJECT_DEFINITION(object_id) AS [Trigger Text]

    FROM

    [sys].[triggers]

    WHERE

    is_disabled = 1;' --disabled

    Thanks again.

    Lee

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Lee Linares (8/7/2015)


    Thanks for taking the time share your script.

    Expanding on Gary's points you could eliminate all the JOINS by using something like this:

    'SELECT

    db_name() as Database_Name,

    name as Trigger_Name,

    OBJECT_NAME(parent_id) as Associated_Table_Name,

    is_ms_shipped,

    OBJECT_DEFINITION(object_id) AS [Trigger Text]

    FROM

    [sys].[triggers]

    WHERE

    is_disabled = 1;' --disabled

    Thanks again.

    Lee

    Thanks for the improvements.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply