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
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
No comments:
Post a Comment