Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to get list of disable and enable triggers from server Expand / Collapse
Author
Message
Posted Sunday, December 06, 2009 11:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 06, 2011 2:12 AM
Points: 27, Visits: 172
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
Post #829664
Posted Monday, December 07, 2009 12:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 06, 2011 2:12 AM
Points: 27, Visits: 172
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)
Post #829683
Posted Monday, December 07, 2009 12:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 3:36 AM
Points: 579, Visits: 1,803
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'



---------------------------------------------------------------------------------
Post #829685
Posted Monday, December 07, 2009 1:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #829691
Posted Monday, December 07, 2009 2:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 06, 2011 2:12 AM
Points: 27, Visits: 172
Thanks for replying..:)
Post #829697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse