how to check trigger is disabled in all database in one shot..?

Question

1.00/5 (2 votes)

See more:

SQL

I want to check whether trigger is disabled or not in all database in one shot in sql..?

Posted 27-May-15 0:51am

Member 11337367

Add a Solution

Comments

King Fisher 27-May-15 7:06am

...

Thanks7872 27-May-15 8:32am

What do you mean by one shot? Why you want to check this? What are you trying to do? This is not a social networking. You have to explain issue properly in order to have a reasonable answer.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kashif Atiq · Answer 1 · 2015-05-27T01:09:00

Solution 1

This query will help you

SQL

SELECT *
FROM sys.triggers

OR

SQL

SELECT
       TAB.name as Table_Name
     , TRIG.name as Trigger_Name
     , TRIG.is_disabled  --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG
inner join sys.tables as TAB
on TRIG.parent_id = TAB.object_id

for finding Disabled trigers in all databases in a server

SQL

you can use sp_MSforeachdb for this purpose like

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT (SELECT DB_NAME()),* FROM sys.triggers WHERE is_disabled = 1'
EXEC sp_MSforeachdb @command

Posted 27-May-15 1:09am

kashif Atiq

Updated 27-May-15 2:55am

v3

Comments

Member 11337367 27-May-15 7:26am

I want to check in all database in one shot..

kashif Atiq 27-May-15 7:40am

so what is the problem with these two queries? they will return you statuses of all the triggers in whole database

Member 11337367 27-May-15 8:14am

I want to change database name each time but i dont want to do like that ..it should return all disabled trigger from all database..

kashif Atiq 27-May-15 8:40am

you can use sp_MSforeachdb for this purpose like

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT (SELECT DB_NAME()),* FROM sys.triggers WHERE is_disabled = 1'
EXEC sp_MSforeachdb @command

Advay Pandya · Answer 2 · 2015-05-27T01:13:00

Solution 2

Please try to use below query. That will return whether the trigger is disabled or not.

SQL

select * from sys.triggers where is_disabled = 1

Please let me know if you have any concern or query.

Thanks
Advay Pandya

Posted 27-May-15 1:13am

Advay Pandya

King Fisher · Answer 3 · 2015-05-27T01:19:00

Solution 3

Refer this::

http://sqltidbits.com/scripts/list-all-disabled-triggers[^]

Posted 27-May-15 1:19am

King Fisher

Thava Rajan · Answer 4 · 2015-05-27T02:56:00

Solution 4

use sys.triggers with msforeachdb

try this link[^]

Posted 27-May-15 2:56am

Thava Rajan