T-SQL语句显示级联依赖关系 .

if exists(select name from sysobjects where name='sp_dependss' and xtype='p')
 drop procedure sp_dependss
go
create procedure sp_dependss --- 1996/08/09 16:51
@objname nvarchar(776)  /* 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 sysname

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

select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
 begin
  raiserror(15250,-1,-1)
  return (1)
 end

/*
**  See if @objname exists.
*/
select @objid = object_id(@objname)
if @objid is null
 begin
  select @dbname = db_name()
  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

 

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

if exists (select *
  from sysdepends
   where depid = @objid)
  select distinct 'name' = (s.name + '.' + o.name),d.depid,d.id,
   type = substring(v.name, 5, 16) into #a
    from sysobjects o, master.dbo.spt_values v, sysdepends d,
     sysusers s
    where o.id = d.id
     and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
     and d.depid = @objid
     and o.uid = s.uid
     and deptype < 2  
begin
 while exists(select distinct 'name' = (s.name + '.' + o.name),d.depid,d.id,
   type = substring(v.name, 5, 16)
    from sysobjects o, master.dbo.spt_values v, sysdepends d,
     sysusers s
    where o.id = d.id
     and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
     and o.uid = s.uid
     and deptype < 2
     and d.depid in(select [id] from #a)
     and d.id not in (select [id] from #a)) 
  
 begin
  
  insert into #a(name,depid,id,type) select distinct 'name' = (s.name + '.' + o.name),d.depid,d.id,
   type = substring(v.name, 5, 16)
    from sysobjects o, master.dbo.spt_values v, sysdepends d,
     sysusers s
    where o.id = d.id
     and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
     and o.uid = s.uid
     and deptype < 2
     and d.depid in(select [id] from #a)
     and d.id not in (select [id] from #a)
 end
 
 select @found_some = 1
 select name,type from #a
end

/*
**  Did we find anything in sysdepends?
*/
if @found_some = 0
 raiserror(15461,-1,-1)

set nocount off

return (0) -- sp_depends

GO

print '1111111111111111111111111测试例子一111111111111111111111111111111111111'
exec sp_depends 'sp_dropdevice'
print '1111111111111111111111111111111111111111111111111111111111111'
exec sp_dependss 'sp_dropdevice'
print '1111111111111111111111111111111111111111111111111111111111111'

print '22222222222222222222222222测试例子二22222222222222222222222222222222222'
exec sp_depends 'syscomments'
print '2222222222222222222222222222222222222222222222222222222222222'
exec sp_dependss 'syscomments'
print '2222222222222222222222222222222222222222222222222222222222222'

--作者：maizc ,珠海科干院05级accp2
--本人不才，只能把系统的sp_depends存储过程修改了一下
 

 

 

=================================================================================================

 

 exec sys.sp_depends main_per

 

 

USE [Per_Rain]
GO
/****** Object:  StoredProcedure [sys].[sp_depends]    Script Date: 10/12/2011 17:32:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_depends]  --- 1996/08/09 16:51
@objname nvarchar(776)  -- 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 sysname

--  Make sure the @objname is local to the current database.

select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
 begin
  raiserror(15250,-1,-1)
  return (1)
 end

--  See if @objname exists.
select @objid = object_id(@objname)
if @objid is null
 begin
  select @dbname = db_name()
  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
 raiserror(15459,-1,-1)
 select   'name' = (s6.name+ '.' + o1.name),
    type = substring(v2.name, 5, 66),  -- spt_values.name is nvarchar(70)
    updated = substring(u4.name, 1, 7),
    selected = substring(w5.name, 1, 8),
             'column' = col_name(d3.depid, d3.depnumber)
  from  sys.objects  o1
   ,master.dbo.spt_values v2
   ,sysdepends  d3
   ,master.dbo.spt_values u4
   ,master.dbo.spt_values w5 --11667
   ,sys.schemas  s6
  where  o1.object_id = d3.depid
  and  o1.type = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
  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.schema_id = s6.schema_id
  and deptype < 2

 select @found_some = 1
end

--  Now check for things that depend on the object.
if exists (select *
  from sysdepends
   where depid = @objid)
begin
  raiserror(15460,-1,-1)
 select distinct 'name' = (s.name + '.' + o.name),
  type = substring(v.name, 5, 66)    -- spt_values.name is nvarchar(70)
   from sys.objects o, master.dbo.spt_values v, sysdepends d,
    sys.schemas s
   where o.object_id = d.id
    and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
    and d.depid = @objid
    and o.schema_id = s.schema_id
    and deptype < 2

 select @found_some = 1
end

--  Did we find anything in sysdepends?
if @found_some = 0
 raiserror(15461,-1,-1)

set nocount off

return (0) -- sp_depends