2 Ways to Return a List of Triggers in a SQL Server Database using T-SQL

If you prefer to administer your SQL Server databases by running T-SQL queries, instead of using the GUI, here are two options for listing all triggers in the current database.

Option 1 – The sys.triggers Catalog View

You can use the sys.triggers system catalog view to get a list of all triggers in a database.

USE Test;
SELECT 
  name,
  OBJECT_NAME(parent_id) AS parent,
  type,
  type_desc
FROM sys.triggers;

Result:

+-------------------+----------+--------+-------------+
| name              | parent   | type   | type_desc   |
|-------------------+----------+--------+-------------|
| t1_insert_trigger | t1       | TR     | SQL_TRIGGER |
| t6ins             | t6       | TR     | SQL_TRIGGER |
+-------------------+----------+--------+-------------+

This view returns all triggers with a type of TR (SQL DML trigger) or TA (Assembly (CLR) DML trigger). It returns both DML trigger names and DDL trigger names (unlike the next option, which only returns DML triggers).

Option 2 – The sys.objects Catalog View

You can also use the sys.objects system catalog view to get a list of triggers.

USE Test;
SELECT
  SCHEMA_NAME(schema_id) AS [schema],
  name,
  OBJECT_NAME(parent_object_id) AS parent,
  type,
  type_desc
FROM sys.objects
WHERE type IN ('TR', 'TA');

Result:

+----------+-------------------+----------+--------+-------------+
| schema   | name              | parent   | type   | type_desc   |
|----------+-------------------+----------+--------+-------------|
| dbo      | t1_insert_trigger | t1       | TR     | SQL_TRIGGER |
| dbo      | t6ins             | t6       | TR     | SQL_TRIGGER |
+----------+-------------------+----------+--------+-------------+

Note that sys.objects only lists DML triggers and not DDL triggers.

This is because sys.objects only returns schema-scoped objects. DML trigger names are schema-scoped but DDL trigger names are scoped by the parent entity. If you need to return DDL triggers, use sys.triggers.