ANALYZE DATABASE DEPENDENCIES

A good system-stored procedure that will help you analyze dependencies
within a database is sp_depends. This procedure will show what database
objects depend on a specified database object and what database objects 
the
specified database object references.

This system-stored procedure is more robust when all objects are 
created
in order of dependence. Objects that are dependant on other objects are
created after the referenced objects are created.

One reason to use this procedure is to identify the impact that making 
a
change to a procedure or a table will have. If you have an object that
references 30 objects, a larger level of effort will exist in coding 
time
in order to change the 30 objects that reference the object in 
question.

The following script demonstrates a stored procedure that references
other objects and the objects that reference the stored procedure:

IF EXISTS(SELECT name
       FROM    sysobjects
       WHERE    name = N'test_table'
       AND    type = 'U')
    DROP TABLE test_table
GO
CREATE TABLE test_table (
c1 VARCHAR(255) NULL)
GO
IF EXISTS (SELECT name
       FROM    sysobjects
       WHERE    name = N'test_proc1'
       AND    type = 'P')
    DROP PROCEDURE test_proc1
GO
CREATE PROCEDURE test_proc1 @name sysname = NULL
AS
IF @name IS NOT NULL
BEGIN
       INSERT test_table VALUES (@name)
END
ELSE
BEGIN
       RETURN
END
GO
IF EXISTS (SELECT name
       FROM    sysobjects
       WHERE    name = N'test_proc2'
       AND    type = 'P')
    DROP PROCEDURE test_proc2
GO
CREATE PROCEDURE test_proc2
AS
DECLARE @myVar sysname
SELECT @myVar = name FROM sysobjects WHERE id = 1
EXEC test_proc1 @myVar
GO
EXEC sp_depends test_proc1
GO
DROP PROCEDURE test_proc2, test_proc1
GO
DROP TABLE test_table
GO

----------------------------------------