Identifying Tables with INSTEAD OF Triggers in SQL Server
In SQL Server, it's sometimes necessary to determine which tables have INSTEAD OF triggers defined on them. These types of triggers override standard actions like INSERT, UPDATE, or DELETE, allowing custom behavior instead of the default database action.
Query to List Tables with INSTEAD OF Triggers
The following SQL query helps you retrieve a list of tables that have at least one INSTEAD OF trigger:
SELECT s.[Name] + N'.' + t.[Name] AS [Table]
FROM [sys].tables t
INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS (
SELECT *
FROM [sys].triggers tr
WHERE tr.parent_id = t.[object_id]
AND tr.is_instead_of_trigger = 1
);
This script joins the sys.tables and sys.schemas system views to display fully qualified table names (i.e., schema.table). It filters for those tables where an INSTEAD OF trigger exists.
Finding Tables Without INSTEAD OF Triggers
If you're looking to find tables without any INSTEAD OF triggers, simply replace EXISTS with NOT EXISTS in the WHERE clause.
