BUG: SP_Depends Does Not List Triggers

• Microsoft SQL Server, version 6.5

SYMPTOMS

The sp_depends system procedure does not list triggers that belong to the table that sp_depends is run for.

CAUSE

Triggers implicitly belong to the table they are created on. Because of this there are no rows created in sysdepends relating a trigger to the table it is created on. The sp_depends system procedure uses the sysdepends table for its information, so the triggers do not show up.

WORKAROUND

Add the following stored procedure to master; use it instead of sp_depends.

if exists (select * from sysobjects where id = object_id('dbo.sp_depends2') and sysstat & 0xf = 4)

   drop procedure dbo.sp_depends2

create procedure sp_depends2 --1996/03/15 12:51

@objname varchar(92)    /* the object we want to check */
as

declare @objid int         /* the id of the object we want */
declare @found_some bit       /* flag for dependencies found */

/*

*/

   substring(@objname, 1, charindex('.', @objname) - 1) <> db_name()
   begin
      raiserror(15250,-1,-1)
      return (1)
   end

/*

*/

   begin
      raiserror(15009,-1,-1,@objname,@dbname)
      return (1)
   end

/*

*/

set nocount on

/*

*/

      from sysdepends
         where id = @objid)

   print 'In the current database the specified object references the

   select       'name' = substring((s6.name + '.' + o1.name), 1, 40),
          type = substring(v2.name, 1, 16),
          updated = substring(u4.name, 1, 7),
          selected = substring(w5.name, 1, 8)
      from   sysobjects    o1
         ,master.dbo.spt_values  v2
         ,sysdepends    d3
         ,master.dbo.spt_values  u4
         ,master.dbo.spt_values  w5 --11667
         ,sysusers      s6
      where  o1.id = d3.depid
      and    o1.sysstat & 0xf = v2.number and v2.type = 'O'
      and    u4.type = 'B' and u4.number = d3.resultobj
      and    w5.type = 'B' and w5.number = d3.readobj|d3.selall
      and    d3.id = @objid
      and    o1.uid = s6.uid

   select @found_some = 1

/*

*/

      from sysdepends
         where depid = @objid)

   print 'In the current database the specified object is referenced by the

   select distinct 'name' = substring((s.name + '.' + o.name), 1, 40),
      type = substring(v.name, 1, 16)
         from sysobjects o, master.dbo.spt_values v, sysdepends d,
            sysusers s
         where o.id = d.id
            and o.sysstat & 0xf = v.number and v.type = 'O'
            and d.depid = @objid
            and o.uid = s.uid
   select @found_some = 1

/*If the object is a table check for triggers  */

  if exists (select deltrig from sysobjects where deltrig = @objid)
   begin
     print "Table has the following triggers: "
     select name from sysobjects where deltrig = @objid
     select @found_some = 1
   end

/*If the object is a trigger list the table it was created on */

  print 'This trigger was created on table:'
  select name from sysobjects where id = (select deltrig from sysobjects

  select @found_some = 1

/*

*/

   print 'Object doesn''t reference any object and no objects reference

set nocount off

return (0) GO

GRANT EXECUTE ON dbo.sp_depends2 TO public GO

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords          : kbbug6.50 SSrvStProc
Version           : 6.5
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbpending