Brett Kaiser (x002548) Blog

This always seems to pop as well.  To determine what objects are dependant on what you can use sp_depends.  Unfortunately that's for 1 level of relationships in either direction (to the parent or the child).  Also, and I don't know why they write them this way, but trying to automate some things using system stored procedures are a pain.  Especially when they return multiple result sets, or when the result have different number of columns.  At the bottom of this post is a rewrite of sp_depends that the script uses.  This script then builds a table of all the relationships in a database, and marries the object to it's dependants and parents.

It's then on to more trees and hierarchies written by Mr. Volk to mine the data.  But at least it's in a managable form now.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_depends_xref ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sp_depends_xref ]
GO
CREATE TABLE sp_depends_xref (
   [name] nvarchar(128)
 , RefType char(2)
 , dep_name nvarchar(256)
 , type   char(16)
 , updated char(7)
 , selected char(8)
 , [column] nvarchar(128))
GO

DECLARE @name sysname, @MAX_name sysname, @sql varchar(8000), @xtype char(2)
SET NOCOUNT ON

  SELECT @name = MIN(name), @MAX_name = MAX(name)
    FROM sysobjects
   WHERE xtype <> 'S'

  SELECT @xtype = xtype FROM sysobjects WHERE [name] = @name

WHILE @name < @MAX_name
  BEGIN

 SELECT @name 

 SELECT @sql = 'INSERT INTO sp_depends_xref(reftype, dep_name, type, updated, selected,[column]) '
 + 'EXEC usp_depends2 [' + @name + ']'

 EXEC(@sql)
 UPDATE sp_depends_xref SET [name] = @name WHERE [name] IS NULL

   SELECT @name = MIN([name])
     FROM sysobjects
    WHERE xtype <> 'S'
      AND [name] > @name
  
   SELECT @xtype = xtype FROM sysobjects WHERE [name] = @name
  END

SELECT @sql = 'INSERT INTO sp_depends_xref(reftype, dep_name, type, updated, selected,[column]) '
+ 'EXEC usp_depends2 [' + @name + ']'

EXEC(@sql)
UPDATE sp_depends_xref SET [name] = @name WHERE [name] IS NULL
GO

SET NOCOUNT OFF
SELECT * FROM sp_depends_xref
GO

SET NOCOUNT OFF
GO

This is the rewite of sp_depends

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_depends2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_Restore_Production]
 DROP PROC usp_depends2
GO

create procedure usp_depends2  --- 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.
*/

DECLARE @sp_depends_xref table (
   reftype char(2)
 , dep_name nvarchar(256)
 , type   char(16)
 , updated char(7)
 , selected char(8)
 , [column] nvarchar(128))

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)
 INSERT INTO @sp_depends_xref (
    refType
  ,  dep_name
  , type
  , updated
  , selected
  , [column])
 select   'TO', 'name' = (s6.name+ '.' + o1.name),
    type = substring(v2.name, 5, 16),
    updated = substring(u4.name, 1, 7),
    selected = substring(w5.name, 1, 8),
             'column' = col_name(d3.depid, d3.depnumber)
  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.xtype = 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.uid = s6.uid
  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)
 INSERT INTO @sp_depends_xref (
    RefType
  , dep_name
  , type)
 select distinct 'BY', 'name' = (s.name + '.' + o.name),
  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 d.depid = @objid
    and o.uid = s.uid
    and deptype < 2

 select @found_some = 1
end

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

 SELECT
    reftype
  , dep_name
  , type
  , updated
  , selected
  , [column]
 FROM @sp_depends_xref

set nocount off

return (0) -- sp_depends