SQL Server Portal

Today, I was working with the trigger and I wanted to get the list of all triggers in the database. It is either enabled or disabled with its details. Finally I developed a script to get all the details in one shot.

For Example : If the trigger is for insert/update/delete or for any other operations, then the given below script will give you all the information related to triggers :

Create PROCEDURE [Get_All_Details_Of_Trigger]
as
Select A.[name] as [Table Name]
,B.[name] as [Trigger Name]
,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsTriggerDisabled') =1
then 'DISABLED' else 'ENABLED' end) as [Trigger Status]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsInsertTrigger') =1
then 'No' else 'Yes' end) as [IsInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstInsertTrigger') =1
then 'No' else 'Yes' end) as [IsFirstInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastInsertTrigger') =1
then 'No' else 'Yes' end) as [IsLastInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsDeleteTrigger') =1
then 'No' else 'Yes' end) as [IsDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstDeleteTrigger') =1
then 'No' else 'Yes' end) as [IsFirstDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastDeleteTrigger') =1
then 'No' else 'Yes' end) as [IsLastDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsUpdateTrigger') =1
then 'No' else 'Yes' end) as [IsUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstUpdateTrigger') =1
then 'No' else 'Yes' end) as [IsFirstUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastUpdateTrigger') =1
then 'No' else 'Yes' end) as [IsLastUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsAfterTrigger') =1
then 'No' else 'Yes' end) as [IsAfterTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsInsteadOfTrigger') =1
then 'No' else 'Yes' end) as [IsInsteadOfTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsTriggerNotForRepl') =1
then 'No' else 'Yes' end) as [IsTriggerNotForReplication]

from sys.tables A
Inner Join sys.triggers B
On A.[object_id]=B.[parent_id]
GO
EXEC [Get_All_Details_Of_Trigger]