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) = '' 
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 
  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 
   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 
  if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 
   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  
  select @l_fnd1 = 0, @l_fnd2 = 0, @l_fnd3 = 0 
  set @l_objCnt = @l_objCnt + 1 
 drop table #sysComments 
 select * from @output 
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

1 comment:

vbda97arpr said...

Feeling lonely? Hook up with Real Singles now for $4.99 to connect, and only $0.99 a min. A true match is only a phone call away. Give it a try 1-800-211-9293.