Detect Object dependencies.

/*  
     Name : Sp_LinkInfo
     Author : Ooi Aik Hooi
     Date : 16th July 2004
     
     Description
     =========

    This stored procedure will detect all first level object dependencies on Stored Procedure.
    The result set will be stored in table Depend. Unlike sp_depends, sp_MSdependencies will be able to detect all 
    link and dependencies associated to an object. It includes TRIGGER, DELETE, UPDATE etc .
  
*/CREATE Procedure sp_linkInfo
As 
Declare @SPName varchar(128),  @tmp varchar(200), @Type varchar(3), @Param varchar(200)
Begin
           if not exists ( select name from sysobjects where name = 'splinktbl'  and Type = 'P')
               Begin
                        Create Table splinktbl
                        (type varchar (10),
                         obj_name varchar (200),
                         owner varchar(10),
                         sequence integer )
              End
          else
                 Delete FROM splinktbl

         if not exists ( select name from sysobjects where name = 'depend'  and Type = 'P')
             Begin
                       Create Table depend
                       (stored_procedure_name varchar (200) ,
           dependent_object varchar (200),
                        dependent_object_type varchar (200))
            End
       else
            Delete from depend 
 
       Declare All_Sp cursor for select name from sysobjects where type = 'P' and name <> 'dtproperties' order by name
       Open All_Sp
       Fetch Next From All_Sp into @SPName

       While @@Fetch_Status = 0
          Begin
                   Insert Into Splinktbl exec sp_MSdependencies @SPName , null, 1053183
                   Declare objcur cursor for select obj_name from splinktbl
                   Open objcur
                   Fetch Next From objcur Into @tmp
                   While @@Fetch_Status = 0
                       Begin  
                                 select @Type = Type from sysobjects where name like @tmp 
                                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                                --    You can add more dependant Type here.Currently, only Stored Procedure, Trigger, Table and View are defined.   
                                -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       if @Type = 'P' 
                                      select @Param = 'Stored Procedure'
                                  else if @Type = 'S'
                                            select @Param = 'System Table'
                                          else if @Type = 'TR'
                                                    select @Param = 'Trigger'
                                                 else if @Type = 'U'
                                                           select @Param = 'Table'
                                                        else if @Type = 'V'
                                                                  select @Param = 'View' 
                                                              else 
                                                                  select @Param = 'Unidentified'                   
                                  Insert into depend values (@SPName,@tmp, @Param)
                                  Fetch Next From Objcur Into @tmp
                       End
                   Delete from splinktbl
                   Close objcur
                   Deallocate objcur
                   Fetch Next From All_Sp Into @SPName
           End
        Close All_Sp
        Deallocate All_Sp
   End
GO