Usp_GetDepObj

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE Usp_GetDepObj 
  @objName   Varchar(200)='',
  @objDepObjTypevarchar(200) ='A'--[T:Table; P:Procedure; A:All]
AS
Declare @SsqlVarchar(8000)


IF Ltrim(Rtrim(@objName)) = '' OR isnull(@objName,'')=''
Begin
Select @objName= ' ID '
End
Else
Begin
Select @objName= ' Object_id('''+@objName+''') '
End
IF  Ltrim(Rtrim(@objDepObjType)) = '' OR isnull(@objDepObjType,'')='' OR upper(@objDepObjType) = 'A'
Begin

Select @objDepObjType = ' Type = ''p'' or Type = ''u'' '

End
IF upper(@objDepObjType) = 'T' or upper(@objDepObjType) = 'U'
Begin
Select @objDepObjType = ' Type = ''u'' '
End
IF upper(@objDepObjType) = 'P'
Begin
Select @objDepObjType = ' Type = ''p'''
End

Select @Ssql = 'Select
object_name(id)As Dependent,object_name(depid) as Parent
From SysDepends 
Where depid = '+@objName+'
And id in(
select id from sysobjects where ' + @objDepObjType +  ' )
Group By object_name(id),object_name(depid) '



Execute( @Ssql)
If @@rowcount = 0 
Begin
print '***************************  No Dependent objects found *************************************'
End
Else
Begin
print '***************************************** Done **********************************************'
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO