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
GO

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 */ 
declare @dbname varchar(30)

/*
**  Make sure the @objname is local to the current database.
*/ 

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

/*
**  See if @objname exists.
*/ 

select @objid = object_id(@objname), @dbname=db_name()
if @objid is null
   begin
      raiserror(15009,-1,-1,@objname,@dbname)
      return (1)
   end

/*
**  Initialize @found_some to indicate that we haven't seen any dependencies.
*/ 

select @found_some = 0

set nocount on

/*
**  Print out the particulars about the local dependencies.
*/ 

if exists (select *
      from sysdepends
         where id = @objid)
begin
   print 'In the current database the specified object references the following:'
   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
end

/*
**  Now check for things that depend on the object.
*/ 

if exists (select *
      from sysdepends
         where depid = @objid)
begin
   print 'In the current database the specified object is referenced by the following:'
   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
end

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

if (select type from sysobjects where id = @objid) = 'U'
begin
  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
end

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

if (select type from sysobjects where id = @objid) = 'TR'
begin
  print 'This trigger was created on table:'
  select name from sysobjects where id = (select deltrig from sysobjects
  where id = @objid)
  select @found_some = 1
end

/*
**  Did we find anything in sysdepends?
*/ 

if @found_some = 0
   print 'Object doesn't reference any object and no objects reference it.'

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.

Additional query words:

Keywords          : SSrvStProc kbbug6.50 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: July 14, 1999