Finding triggers on table or schema in SQL Server DB

In an earlier article, we have seen a simple tip for finding triggers which are modified during a given date range. Now we will see how to find the triggers which are created against a table or multiple tables with similar partial name or a schema. Let us see them one by one.

Finding triggers on a table

It is easy to find triggers created against a table. All you have to do is Expand the table in the SSMS or Azure Data Studio‘s left panel and then expand the Triggers folder.

Listing triggers on tables having similar partial name

However, listing all the triggers created against tables which have similar partial names is not straightforward. You have to use the sys table sys.triggers for finding the triggers. Here is the script:

SELECT
    Name as 'Trigger', 
    OBJECT_NAME(parent_id) AS 'Table'
FROM
   sys.triggers
WHERE
   OBJECT_NAME(parent_id) LIKE '%_A'
GO

Listing triggers on tables under a schema

Likewise, here is the script to list all the triggers against tables within a schema. In this illustration, Purchasing is the name of the schema.

SELECT
    Name as 'Trigger', 
    OBJECT_SCHEMA_NAME(parent_id) AS 'Schema', 
    OBJECT_NAME(parent_id) AS 'Table'
FROM
   sys.triggers
WHERE
   OBJECT_SCHEMA_NAME(parent_id) = 'Purchasing'
GO

Reference

• More about sys.triggers at Microsoft Docs.