Wednesday, January 22, 2020

Tables with at least two triggers

SQL Query to Find Tables with Multiple Triggers

This SQL query is designed to find tables in a database that have more than one trigger associated with them.

DECLARE @min_count INT;
SET @min_count = 2;

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM [sys].triggers tr
  WHERE tr.parent_id = t.[object_id]
  GROUP BY tr.parent_id
  HAVING COUNT(*) >= @min_count
);

This query checks the system tables and triggers to identify those tables that contain two or more triggers. The @min_count variable is used to set the threshold for the number of triggers a table must have.

at

Popular Posts