Table trigger relationship

• vambati

  SSCrazy

  Points: 2769

  More actions

  January 28, 2004 at 2:43 pm

  #160410

  I have quite a few triggers in our databases. Is there any way by which i can get the dependent tables&column names which are in the triggers of the main table. Ex: Table A has Utrig and Itrig

                       I need to know the tables and column names the triggers are

  referencing for table A. Any help would be appreciated.

  Bobby.

• Chris_Tuck

  SSC Enthusiast

  Points: 127

  More actions

  January 29, 2004 at 3:53 am

  #492135

  The following may go some way to providing the information you seek:

  Select sdid.name as [Trigger Name], sdepid.name as [Dependent Object Name],

  (Case sdepid.xtype

  when 'P' then 'Procedure'

   when 'TR' then 'Trigger'

   when 'U' then 'User Table'

   else null End) as [Dependent Object Type],

  sd.depnumber as [Dependent Object Reference],

  (Select sc.name from syscolumns sc (NOLOCK)

   where sc.id = sd.depid

   and sc.colid = sd.depnumber) as [Dependent Object Column or Parameter],

  (Case when (sd.resultobj = 1) then 'Yes' else 'No' End) as [Updated],

  (Case when (sd.readobj = 1) then 'Yes' else 'No' End) as [Read only]

  from sysdepends sd (NOLOCK)

  inner join sysobjects sdid (NOLOCK) on

   sd.id = sdid.id

  inner join sysobjects sdepid (NOLOCK) on

   sd.depid = sdepid.id

  where (sdid.xtype = 'TR') and (sdid.status > 0)

  Order by sdid.name, sd.depnumber

  GO

  Hope this helps . . .

  Chris