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 
