Search for an expression in the body of programmable objects

,

Sometimes you want to modify an object (like a stored procedure, Function, trigger
and view) and you can not

remember the name of that object, but you remember some
words of the comments that you placed in the body of that

object.

If this is your case, you can use the following procedures depending on what version of SQL Server you are using.
It will

search all the databases except system databases and if it finds any match, it will return the name and the type of that object.

By the way, this stored procedure is on its second edition.

Usage:

Consider you have the following comment in the body of your object:

"This is a test to test the efficiency of the stored proc!"

You can invoke the proc in the following ways:

Exec Usp_SearchInBodyOfCodes 'efficiency "This is a test" ', @flag='w'

It will search for the words "efficiency" and "This is a test" in the body of mentioned objects.

Exec Usp_SearchInBodyOfCodes 'efficiency "This is a test" ', @flag='a'

Will search for any word of "efficiency" or "This is a test" n the body of the objects

Consideration:

Best to view the results in Query Analyzer Results in Text (Control

+T)
you can limit the databases in which you want Have fun with it, and if you like it vote for it....

/*==============================================================
SQL SERVER 2000 version:
==============================================================*/

Create proc Usp_SearchInBodyOfCodes (@s varchar(400),
				     @flag char(1))

as 

/*******************************************************************************

  Written By  	: yousef ekhtiari
  Email         :y_ekhtiari@yahoo.com
  Create  Date  : 10 January 2006
  Modified Date :19 November 2006
  Description 	: Returns the name of   stored procedures,all kinds of functions
				which contain whole or any part of  expression
				in a string


  
USAGE:

@flag='a' means any part of  expression
@flag='w' whole  part of  expression

exec Usp_SearchInBodyOfCodes  @s='#tmp_result   "yousef  ekhtiari"    ',@flag='w'
exec Usp_SearchInBodyOfCodes  @s='#tmp_result   "yousef  ekhtiari"  lol  ',@flag='a'


********************************************************************/
set nocount on
create table #Sarg   (s varchar(100)  )
declare 
		@pos int,
		@sSQL varchar(8000),
		 @dbname as sysname,
		@where as  varchar(8000),
		@collation as varchar(200)

	if @flag not in ('w','a')
	begin
		raiserror('Invalid use of @flag',16,1)
		return	
	end
set @s=ltrim(ltrim(@s))+' '

while len(@s)>0
begin
	if left(@s,1)='"'
		begin
			set  @pos=CHARINDEX('"',@s,2)
			insert  #Sarg values( ltrim(replace( left(@s,CHARINDEX('"',@s,2) ) ,'"','')))
	
		end 
	else
		begin
			set  @pos=CHARINDEX(' ',@s,2)
			insert  #Sarg values(  ltrim(left(@s,CHARINDEX(' ',@s,2))))
	
		end
	set @s=ltrim(stuff(@s,1,@pos ,''))

end
declare db cursor 
for  SELECT [name],convert(varchar(100),DATABASEPROPERTYEX([name], 'Collation') )
FROM [master].[dbo].[sysdatabases]
/*where sid<>0x01--excludes all system databases attention: I already thought the system databases have sid equal to 0x01 but I 

came across a user database with sid equal to 0x01
so I decided to alter the code*/
where name not in('master','tempdb','msdb','distribution','model')
open db
fetch next from db into @dbname,@collation
while @@fetch_status=0
begin
	print '----------------'+ @dbname+'------------------------'

		set @sSQL='SELECT  distinct [name]=cast([name]as varchar(30)) ,type=case  type
                                            when  ''p'' then ''stored procedure''
                                            when  ''V'' then ''stored procedure''
                                            when  ''TR'' then ''trigger''
                                            when  ''TF'' then ''table-valued-function''
                                            when  ''IF'' then ''inlined table-valued function''
                                            when  ''FN'' then ''scalar function''
                                            end 
		FROM  '+@dbname+'.[dbo].[sysobjects] o
		inner join  '+@dbname+'.[dbo].[syscomments] c on o.id=c.id
		where xtype in (''p'',''V'', ''TR'', ''TF'', ''IF'', ''FN '')
		and name     not like ''dt_%'''
if @flag='a'
	set @sSQL=@sSQL+' and   exists(
						select * from #Sarg
						where 
						 ltrim(rtrim(text))    like N''%''+ltrim(rtrim(s))+''%''  COLLATE 

 '+@collation+')'
else if @flag='w'
		begin
			set @where=''

			select @where=@where+' and  patindex( ''%'+replace(ltrim(rtrim(s)),'''','''''' )+'%'', text)>0'
			from   #Sarg 
			
			set @sSQL=@sSQL+@where

		end

	
	exec(@sSQL)
fetch next from db into @dbname,@collation

end
close db
deallocate db
drop table  #Sarg
/*==============================================================
SQL SERVER 2005 version:
==============================================================*/

Create proc Usp_SearchInBodyOfCodes (@s varchar(400),
                                     @flag char(1))

as 

/*******************************************************************************

  Written By                   : yousef ekhtiari
  Email                        :y_ekhtiari@yahoo.com
  Create  Date                 : 10 January 2006
  Modified for new SQL Version :19 November 2006
  Description : Returns the name of   stored procedures,all kinds of functions ,views and triggers
                which contain whole or any part of  expression
                in a string


  
USAGE:

@flag='a' means any part of  expression
@flag='w' whole  part of  expression

exec Usp_SearchInBodyOfCodes  @s='#tmp_result   "yousef  ekhtiari"    ',@flag='w'
exec Usp_SearchInBodyOfCodes  @s='#tmp_result   "yousef  ekhtiari"  lol  ',@flag='a'


********************************************************************/
set nocount on
create table #Sarg   (s varchar(100)  )
declare 
        @pos int,
        @sSQL varchar(8000),
         @dbname as sysname,
        @where as  varchar(8000),
        @collation as varchar(200)

    if @flag not in ('w','a')
    begin
        raiserror('Invalid use of @flag',16,1)
        return	
    end
set @s=ltrim(ltrim(@s))+' '

while len(@s)>0
begin
    if left(@s,1)='"'
        begin
            set  @pos=CHARINDEX('"',@s,2)
            insert  #Sarg values( ltrim(replace( left(@s,CHARINDEX('"',@s,2) ) ,'"','')))
    
        end 
    else
        begin
            set  @pos=CHARINDEX(' ',@s,2)
            insert  #Sarg values(  ltrim(left(@s,CHARINDEX(' ',@s,2))))
    
        end
    set @s=ltrim(stuff(@s,1,@pos ,''))

end
declare db cursor 
for  SELECT [name],convert(varchar(100),DATABASEPROPERTYEX([name], 'Collation') )
FROM sys.databases
where name not in('master','tempdb','msdb','distribution','model')
open db
fetch next from db into @dbname,@collation
while @@fetch_status=0
begin
    print '----------------'+ @dbname+'------------------------'

        set @sSQL='SELECT  distinct [name]=cast([name]as varchar(30))                                         ,type=case  type
                                            when  ''p'' then ''stored procedure''
                                            when  ''V'' then ''stored procedure''
                                            when  ''TR'' then ''trigger''
                                            when  ''TF'' then ''table-valued-function''
                                            when  ''IF'' then ''inlined table-valued function''
                                            when  ''FN'' then ''scalar function''
                                            end 
        FROM  '+@dbname+'.sys.objects o
        inner join  '+@dbname+'.sys.sql_modules c on o.object_id=c.object_id
        where type in (''p'',''V'', ''TR'', ''TF'', ''IF'', ''FN '')'
if @flag='a'
    set @sSQL=@sSQL+' and   exists(
                        select * from #Sarg
                        where   definition    like N''%''+ltrim(rtrim(s))+''%''  COLLATE  '+@collation+')'
else if @flag='w'
        begin
            set @where=''

            select @where=@where+' and  patindex( ''%'+replace(ltrim(rtrim(s)),'''','''''' )+'%'', definition)>0'
            from   #Sarg 
            
            set @sSQL=@sSQL+@where

        end

    
    exec(@sSQL)
fetch next from db into @dbname,@collation

end
close db
deallocate db
drop table  #Sarg

Rate

Share

Share

Rate