INF: A Stored Procedure to Display Trigger Information

   if exists (select * from sysobjects where id =
   object_id('sp_helptrigger') and sysstat & 0xf = 4)
   drop procedure sp_helptrigger
   GO

   create procedure sp_helptrigger @object_name varchar(30) = null
   as

   /* This stored procedure can be used to display information on triggers
   including
   ** what table the trigger is defined on.
   **
   ** Parameter:
   ** @object_name.
   **
   ** If @object_name is a database all triggers will be displayed for that
   database.
   ** If @object_name is a table all triggers created on that table will be
   displayed.
   ** If @object_name is a trigger a single triggers will be reported.
   ** If @object_name is null all triggers in the current database will be
   reported.
   **
   ** Version History:
   ** Date      Description Of Changed          Changed By
   ** 14-04-97  Original Version                Gerard Conroy
   */

   declare @object_id int, @db_len tinyint, @tab_len tinyint, @trg_len
   tinyint,
   @q1 varchar(255), @q2 varchar(255), @db_name varchar(30)

   create table #temp1 (tr_name varchar(30) null, tr_id int, table_id int,
   table_name varchar(30) null, tr_type char(1))

   /*
   ** If object name is null display everything for current database
   */
   if @object_name is null
   begin
   select @db_name = db_name()
   goto display_output
   end

   select @object_name = rtrim(ltrim(@object_name))

   /*
   ** If object name is a database display everything for specified
   database
   */

   if exists (select * from master..sysdatabases where name = @object_name)
   begin
      select @db_name = @object_name
      goto display_output
   end

   select @db_name = db_name()

   /*
   ** If object name is a table display everything for the specified table
   */
   if exists (select * from sysobjects where name = @object_name and type
   in ('U','S'))
   begin

   create table #temp2 (tr_name varchar(30) null, tr_id int, table_id int,
   table_name varchar(30) null, tr_type char(1))

   insert into #temp2
   select name, id, deltrig, null, 'X'
   from sysobjects
         where type = 'TR'
   and id = (select deltrig from sysobjects where name=@object_name and
   deltrig <> 0)

   insert into #temp2
   select name, id, deltrig, null, 'X'
   from sysobjects
         where type = 'TR'
   and id = (select updtrig from sysobjects where name=@object_name and
   updtrig <> 0)

   insert into #temp2
   select name, id, deltrig, null, 'X'
   from sysobjects
         where type = 'TR'
   and id = (select instrig from sysobjects where name=@object_name and
   instrig <> 0)

   insert into #temp1 select distinct tr_name, tr_id, table_id, table_name,
   tr_type from #temp2

   goto display_with_temp
   end

   /*
   ** If object name is a trigger null display everything for specified
   trigger
   */
   if exists (select * from sysobjects where name = @object_name and type =
   'TR')
   begin

   insert into #temp1
   select name, id, deltrig, null, 'X'
   from sysobjects
         where type = 'TR'
   and name = @object_name

   goto display_with_temp
   end

   /*
   ** Invalid object name
   */
   select 'Warning:' = 'Parameter not recognised. Acceptable parmeters are
   database name, table name, trigger name or null.'
   return 4

   /*
   ** Format and display output.
   */

   display_output:

   select @q1 = "insert into #temp1 select name, id, deltrig, null, 'X'
   from "
   select @q2 = @db_name + "..sysobjects where type = 'TR'"

   exec(@q1 + @q2)

   /*
   ** Execute from this point if #temp1 was created earlier.
   */

   display_with_temp:

   select @q1 = "update #temp1 set table_name = name, tr_type = (case when
   deltrig = tr_id then 'D' when instrig = tr_id then 'I' when updtrig =
   tr_id then 'U' end ) from #temp1, "
   select @q2 = @db_name + "..sysobjects where table_id = id and type in
   ('U','S')"

   exec(@q1 + @q2)

   select @db_len = datalength(@db_name) + 2
   if (@db_len < 10) select @db_len = 10

   select @tab_len = (select max(datalength(table_name)) from #temp1) + 2
   if (@tab_len < 7 or @tab_len is null) select @tab_len = 7

   select @trg_len = (select max(datalength(tr_name)) from #temp1) + 2
   if (@trg_len < 9 or @trg_len is null) select @trg_len = 9

   select @q1 = "select 'database' = convert(varchar(" +
   convert(char(2),@db_len)
   select @q1 = @q1 + "), """ + @db_name + """), 'table' =
   convert(varchar(" + convert(char(2),@tab_len)
   select @q2 = "), table_name), 'trigger' = convert(varchar(" +
   convert(char(2),@trg_len)
   select @q2 = @q2 + "), tr_name), 'trigger type' = case tr_type when 'D'
   then 'delete' when 'I' then 'insert' when 'U' then 'update' else
   'unknown' end from #temp1"

   exec (@q1 + @q2)

   select @q1 = 'Enter sp_helptext <trigger name> to find out more '
   select @q1 = @q1 + 'about how a trigger is defined'

   select 'Remarks:' = @q1

   return 0

   go