Need to get list of disable and enable triggers from server

  • 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

  • Got it.:)

    Declare @sql nvarchar(MAX)

    Set @sql = ''

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

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

    t.is_disabled

    from sysobjects o

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

    inner join sys.triggers t on t.object_id = o.id

    where o.type=''TR''

    '

    from master..sysdatabases

    Where DBID > 4 --skip the system databases

    EXEC (@sql)

  • Another way would be this!

    select CASE OBJECTPROPERTY(OBJECT_ID(name), 'ExecIsTriggerDisabled')

    WHEN 1 THEN 'Disabled'

    ELSE 'Enabled' END as Status

    from sysobjects o

    where o.type='TR'

    ---------------------------------------------------------------------------------

  • You can use the objectproperty function to check if the trigger is enabled. If you don’t mind getting it in few recordsets, you can also use the undocumented procedure sp_MSforeachdb that will run the statement on all databases. Notice that since this is an undocumented procedure I use it on my own scripts, but I never use it in a real production application.

    exec sp_MSforeachdb 'use ? if DB_ID()>4 select ''?'' as DBName, name,

    objectproperty(object_id,''ExecIsTriggerDisabled'') as IsDisabled

    from sys.objects

    where type = ''TR'''

    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/

  • Thanks for replying..:)

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

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