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
.