Microsoft KB Archive/180490

= BUG: SP_Depends Does Not List Triggers =

Article ID: 180490

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q180490



BUG #: 15457 (6.5)

BUG #: 223465 (Shiloh_bug)



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 that this is a problem in the Microsoft products that are listed at the beginning of this article.

Keywords: kbbug kbpending KB180490

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.