How to select all the triggers in an mssql table

I've found this script somewhere on the internet, just for reference:
SELECT
 [Table] = OBJECT_NAME(o.parent_obj),
 [Trigger] = o.[name],
 [Type] = 
 CASE WHEN
  (
  SELECT
   cmptlevel
  FROM
   master.dbo.sysdatabases
  WHERE
   [name] = DB_NAME()
  ) = 80 
 THEN
  CASE WHEN OBJECTPROPERTY(o.[id], 'ExecIsInsteadOfTrigger') = 1 
   THEN 'Instead Of'
   ELSE 'After'
  END
 ELSE 'After'
 END,
 [Insert] = 
 CASE WHEN
  OBJECTPROPERTY(o.[id],
  'ExecIsInsertTrigger') = 1 
 THEN 'Yes'
 ELSE
  'No'
 END,
 [Update] = 
 CASE WHEN
  OBJECTPROPERTY(o.[id],
  'ExecIsUpdateTrigger') = 1 
 THEN
  'Yes'
 ELSE
  'No'
 END,
 [Delete] = 
 CASE WHEN
  OBJECTPROPERTY(o.[id],
  'ExecIsDeleteTrigger') = 1 
 THEN
  'Yes'
 ELSE
  'No'
 END,
 [Enabled?] = 
 CASE WHEN
  OBJECTPROPERTY(o.[id],
  'ExecIsTriggerDisabled') = 0 
 THEN
  'Enabled'
 ELSE
 'Disabled'
 END
FROM sysobjects o
WHERE
 OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
 -- leave out the following clause if you need to
 -- include system triggers, e.g. those in MSDB
AND
 OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0
ORDER BY 1,2
How to select all the triggers in an mssql table How to select all the triggers in an mssql table Reviewed by Edward Legaspi on Sunday, May 20, 2012 Rating: 5

No comments:

Powered by Blogger.