List Out All User Defined Triggers

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  July 7, 2015 at 2:06 pm

  #305696

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

  Thanks.

• Sporidium

  SSCommitted

  Points: 1550

  More actions

  August 7, 2015 at 3:16 am

  #1818034

  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: 2727

  More actions

  August 7, 2015 at 6:51 am

  #1818074

  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

  More actions

  August 18, 2015 at 7:06 am

  #1820698

  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.