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