This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script for getting the list of Triggers in SQL Server. Periodically, we should do the auditing of database hidden triggers.
Because triggers are not good for database performance especially for OLTP system. Many times developers are creating triggers for testing purpose and later forget to drop those.
SQL Server: Encrypted DDL Trigger to Track all Database Changes
Execute the below script and get list of all triggers:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT sysobjects.name AS TriggerName ,USER_NAME(sysobjects.uid) AS TriggerOwner ,s.name AS TableSchema ,OBJECT_NAME(parent_obj) AS TableName ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS Isupdate ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS Isdelete ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS Isinsert ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS Isafter ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS Isinsteadof ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS Isdisabled FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE sysobjects.type = 'TR' |
Leave a Reply