2

I have database in SQL Server 2008 with triggers on almost every table. I need to review tables without active triggers.
How to get list of all tables without any active triggers (tables without any triggers or those tables where all triggers are disabled)?

Marek Kwiendacz
  • 9,524
  • 14
  • 48
  • 72

1 Answers1

5

You can anti semi join between sys.tables and sys.triggers.

e.g. with NOT IN

SELECT *
FROM   sys.tables
WHERE  object_id NOT IN (SELECT parent_id
                         FROM   sys.triggers
                         WHERE  is_disabled = 0)

Or NOT EXISTS 

SELECT *
FROM   sys.tables t
WHERE  NOT EXISTS (SELECT *
                   FROM   sys.triggers tr
                   WHERE  is_disabled = 0
                          AND tr.parent_id = t.object_id)

Neither sys.tables.object_id or sys.triggers.parent_id are nullable and in this case both give the same semantics and plan.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • @ErikE - That's my default position too but here both `object_id` and `parent_id` are `NOT NULL`-able so will give the same plan as `NOT EXISTS` – Martin Smith Jun 06 '13 at 18:42
  • I just don't like `IN` syntax... *ever*, except for a list of scalars or column names. :) – ErikE Jun 06 '13 at 18:51
  • Added the `NOT EXISTS` version. – Martin Smith Jun 06 '13 at 19:06
  • Of course you are 100% right that the `IN` is just fine here. I have seen some nasty triply- or quadruply-nested `IN` queries, though--where joins or `EXISTS` to a single `INNER JOIN`ed set of tables would have been far superior. So I lean away from `IN` because it seems to help foster this kind of messy query thinking in the inexperienced (which you are *not*). – ErikE Jun 06 '13 at 19:26