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
- More about sys.triggers at Microsoft Docs.