December 6, 2009 at 11:46 pm
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
December 7, 2009 at 12:53 am
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)
December 7, 2009 at 12:56 am
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'
---------------------------------------------------------------------------------
December 7, 2009 at 1:14 am
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/
December 7, 2009 at 2:04 am
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