In our production environment, every user table has an auto-generated trigger that facilitates auditing. Since trigger generation is an automated process, it occasionally fails to run for a plethora a reasons; this it is a good practice to periodically scan for tables that are missing triggers. The following query will return tables that fit this condition by using a LEFT JOIN between sysobjects and itself.
SELECT sys1.name
FROM sysobjects sys1
LEFT JOIN sysobjects sys2
ON sys1.id = sys2.parent_obj
AND sys2.xtype = ‘TR’
WHERE sys2.name IS NULL
AND sys1.xtype = ‘U’
ORDER BY sys1.name