Need to find list of diable and enable triggers

• pols

  SSC Eights!

  Points: 935

  More actions

  December 6, 2009 at 11:45 pm

  #207025

  Hi,

  I need to findout the list of enable and disable triggers from all the databses in server.

  Pls help me.

  The below query to get the list of triggers from all the dbs in server.But i need to know the disable and enable triggers in this.

  Declare @sql varchar(MAX)

  Set @sql = ''

  Select @sql = @sql + 'USE ' + name + '

  select '''+name+''' as DBName, p.name as [TableName], o.name, o.deltrig, o.instrig, o.updtrig

  from sysobjects o

  left outer join sysobjects p ON p.id = o.parent_obj

  where o.type=''TR''

  '

  from master..sysdatabases

  Where DBID > 4 --skip the system databases

  EXEC (@sql)

  Thanks

• dmoldovan

  SSCertifiable

  Points: 6334

  More actions

  December 7, 2009 at 5:48 am

  #1088457

  Use a cursor to loop through databases or the sp_MSforeachdb (undocumented) procedure, see

  http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

  You can also query the sys.triggers catalog view.

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  December 7, 2009 at 6:03 am

  #1088460

  Pleas don't double post the questions. This question was already answered here:

  http://www.sqlservercentral.com/Forums/Topic829664-149-1.aspx

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/