Wednesday, July 13, 2005

How to find Perticuler Words or Sentences in Procedure/Function

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  
 
-------------------------------------------------------------------------------------------------------------------------------------- 
 
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


Start your day with Yahoo! - make it your home page

No comments: