How to find dependent Objects (Sproc, Function or View) or How to find Perticuler Words or sentences in Procedure/Function
For Example : If you want to Search the words "Modified By : Ritesh Kesharwani" in All Used Objects like Function, Procedure, View or Triggrs.
Execute below Procedure in your SQL Server. to Run that Procedure User must have Administrator rights.
--------------------------------------------------------------------------------------------------------------------------------------
Create proc ALL_sp_depends
@p_c1 nvarchar(100),
@p_c2 nvarchar(100) = '',
@p_c3 nvarchar(100) = ''
as
begin
set nocount on
print @p_c2
print @p_c3
declare @l_objname nvarchar(100), @l_Objid int, @l_type as nvarchar(10)
declare @l_text varchar(8000), @l_fnd1 bit, @l_fnd2 bit, @l_fnd3 bit
declare @l_objCnt int, @l_commCnt int
declare @l_totObjCnt int, @l_TotCommCnt int
select @l_objCnt = 1, @l_commCnt = 1
select @p_c1 = '%' + @p_c1 + '%', @p_c2 = case when @p_c2 in('',null) then '' else '%' + @p_c2 + '%' end,
@p_c3 = case when @p_c3 in('',null) then '' else '%' + @p_c3 + '%' end
declare @output table(ObjectName nvarchar(200), Type nvarchar(50))
declare @sysobjs table (tid int identity(1,1) primary key, objname nvarchar(200), objid int, type nvarchar(10))
create table #sysComments(tid int identity(1,1) primary key, ObjText varchar(8000))
insert into @sysobjs select name, id, Type from sysobjects
set @l_totObjCnt = @@rowcount
while @l_objCnt <= @l_totObjCnt
begin
select @l_objname = objname, @l_Objid = objid,@l_type = type from @sysobjs where tid = @l_objCnt
set @l_commCnt = 1
delete from #sysComments
DBCC CHECKIDENT (#sysComments, RESEED, 0)
insert into #sysComments select text from syscomments where id = @l_Objid
set @l_TotCommCnt = @@rowcount
while @l_commCnt <= @l_TotCommCnt
begin
set @l_text = ''
select @l_text = ObjText from #sysComments where tid = @l_commCnt
if patindex(@p_c1, @l_text) > 0
set @l_fnd1 = 1
if @p_c2 = ''
set @l_fnd2 = 1
else if patindex(@p_c2, @l_text) > 0
set @l_fnd2 = 1
if @p_c3 = ''
set @l_fnd3 = 1
else if patindex(@p_c3, @l_text) > 0
set @l_fnd3 = 1
if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 break
set @l_commCnt = @l_commCnt + 1
end
if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1
begin
insert into @output values(@l_objname,
case @l_type when 'C' then 'CHECK constraint'
when 'D' then 'Default or DEFAULT constraint'
when 'F' then 'FOREIGN KEY constraint'
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'K' then 'PRIMARY KEY or UNIQUE constraint'
when 'L' then 'Log'
when 'P' then 'Stored procedure'
when 'R' then 'Rule'
when 'RF' then 'Replication filter stored procedure'
when 'S' then 'System table'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User table'
when 'V' then 'View'
when 'X' then 'Extended stored procedure'
else 'Other' end
)
end
select @l_fnd1 = 0, @l_fnd2 = 0, @l_fnd3 = 0
set @l_objCnt = @l_objCnt + 1
end
drop table #sysComments
select * from @output
end
@p_c1 nvarchar(100),
@p_c2 nvarchar(100) = '',
@p_c3 nvarchar(100) = ''
as
begin
set nocount on
print @p_c2
print @p_c3
declare @l_objname nvarchar(100), @l_Objid int, @l_type as nvarchar(10)
declare @l_text varchar(8000), @l_fnd1 bit, @l_fnd2 bit, @l_fnd3 bit
declare @l_objCnt int, @l_commCnt int
declare @l_totObjCnt int, @l_TotCommCnt int
select @l_objCnt = 1, @l_commCnt = 1
select @p_c1 = '%' + @p_c1 + '%', @p_c2 = case when @p_c2 in('',null) then '' else '%' + @p_c2 + '%' end,
@p_c3 = case when @p_c3 in('',null) then '' else '%' + @p_c3 + '%' end
declare @output table(ObjectName nvarchar(200), Type nvarchar(50))
declare @sysobjs table (tid int identity(1,1) primary key, objname nvarchar(200), objid int, type nvarchar(10))
create table #sysComments(tid int identity(1,1) primary key, ObjText varchar(8000))
insert into @sysobjs select name, id, Type from sysobjects
set @l_totObjCnt = @@rowcount
while @l_objCnt <= @l_totObjCnt
begin
select @l_objname = objname, @l_Objid = objid,@l_type = type from @sysobjs where tid = @l_objCnt
set @l_commCnt = 1
delete from #sysComments
DBCC CHECKIDENT (#sysComments, RESEED, 0)
insert into #sysComments select text from syscomments where id = @l_Objid
set @l_TotCommCnt = @@rowcount
while @l_commCnt <= @l_TotCommCnt
begin
set @l_text = ''
select @l_text = ObjText from #sysComments where tid = @l_commCnt
if patindex(@p_c1, @l_text) > 0
set @l_fnd1 = 1
if @p_c2 = ''
set @l_fnd2 = 1
else if patindex(@p_c2, @l_text) > 0
set @l_fnd2 = 1
if @p_c3 = ''
set @l_fnd3 = 1
else if patindex(@p_c3, @l_text) > 0
set @l_fnd3 = 1
if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 break
set @l_commCnt = @l_commCnt + 1
end
if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1
begin
insert into @output values(@l_objname,
case @l_type when 'C' then 'CHECK constraint'
when 'D' then 'Default or DEFAULT constraint'
when 'F' then 'FOREIGN KEY constraint'
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'K' then 'PRIMARY KEY or UNIQUE constraint'
when 'L' then 'Log'
when 'P' then 'Stored procedure'
when 'R' then 'Rule'
when 'RF' then 'Replication filter stored procedure'
when 'S' then 'System table'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User table'
when 'V' then 'View'
when 'X' then 'Extended stored procedure'
else 'Other' end
)
end
select @l_fnd1 = 0, @l_fnd2 = 0, @l_fnd3 = 0
set @l_objCnt = @l_objCnt + 1
end
drop table #sysComments
select * from @output
end
--------------------------------------------------------------------------------------------------------------------------------------
How To Use This Function:
Exec ALL_sp_depends 'Modified By : Ritesh Kesharwani'
It will give you ObjectName and Type of the procedure/ view/function where that strings used.
this function can take three parameter like table name , column name and other , last two parameter is not mandatory.
Have a Nice Day 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell : 011-91-9845657636
Page: www.Riteshk.blogspot.com
Start your day with Yahoo! - make it your home page
