August 24, 2007 at 7:54 am
i've got hundreds of procedures, and i'm looking for one which may
contain some custom alert messages, but need to determine which procedure
it is without opening each and every one (which would take a while).
i was wondering if there was some other way to search through all the procedures
for certain words?
ideas?
thanks in advance.
_________________________
August 24, 2007 at 8:03 am
I use the following stored proc compiled in the master database. Use at your own risk..
USE
[master]
GO
/****** Object: StoredProcedure [dbo].[sp_FindCode] Script Date: 08/24/2007 09:01:47 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
/*==============================================================
SQL SERVER 2005 version:
==============================================================*/
ALTER
proc [dbo].[sp_FindCode]
(
@S varchar(400),
@flag
char(1) = a, -- anywhere search is the default
@dbToSearch
sysname = null) -- if null then search all non-system databases, othewise only in given db
as
/*******************************************************************************
Written By : yousef ekhtiari
Email : y_ekhtiari@yahoo.com
Create Date : 10 January 2006
Modified for new SQL Version : 19 November 2006
Modified By : Anders Pedersen
Email : arrowdrive@hotmail.com
Modified Date : 30 November 2006
Modified to work from any database when created in the master database.
Modified to return an easier to read result set when having many databases.
Modified to work with databases with space in the database name.
Modified to work on a particular database (including system databases) or all databases
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
@dbToSearch = null, search all non system databases
@dbToSearch <> null, search only the given database
exec Usp_SearchInBodyOfCodes @S='#tmp_result "Anders Pedersen" ',@flag='w'
exec Usp_SearchInBodyOfCodes @S='#tmp_result "Anders Pedersen" lol ',@flag='a'
********************************************************************/
set
nocount on
set
quoted_identifier off
create
table #Sarg (s varchar(100) )
create
table #Res (dbname sysname,
ObjectName
sysname,
Type varchar(30))
declare
@pos
int,
@sSQL
varchar(max),
@dbname
as sysname,
@where
as varchar(max),
@collation
as varchar(200),
@dbname2
as sysname
if @flag not in ('w','a')
begin
raiserror('Invalid use of @flag',16,1)
return
end
if @dbToSearch is not null
begin
if not exists (select name from sys.databases where name = @dbToSearch)
begin
raiserror('Invalid database name',16,1)
return
end
end
set
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
if
@dbToSearch is null
declare db cursor
for SELECT [name],convert(varchar(100),DATABASEPROPERTYEX([name], 'Collation') )
FROM sys.databases
where name not in('master','tempdb','msdb','distribution','model')
if
@dbToSearch is not null
declare db cursor
for SELECT [name],convert(varchar(100),DATABASEPROPERTYEX([name], 'Collation') )
FROM sys.databases
where name = @dbToSearch
open
db
fetch
next from db into @dbname,@collation
while
@@fetch_status=0
begin
select @dbname2 = '[' + @dbname + ']'
set @sSQL='SELECT distinct "' + @dbname + '" as dbname,[name] ,type=case type
when ''p'' then ''stored procedure''
when ''V'' then ''view''
when ''TR'' then ''trigger''
when ''TF'' then ''table-valued-function''
when ''IF'' then ''inlined table-valued function''
when ''FN'' then ''scalar function''
end
FROM '
+@dbname2+'.sys.objects o
inner join '
+@dbname2+'.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
insert into #Res
exec(@sSQL)
--select @ssql
fetch
next from db into @dbname,@collation
end
close
db
deallocate
db
select
* from #Res
drop
table #Sarg
August 24, 2007 at 8:10 am
wow.... cool! thanks!
oops... need the initial code to create sp_FindCode
_________________________
August 24, 2007 at 8:22 am
You can also use this, I think it's simplier:
select
distinct object_name(id)
from sys.syscomments
where text like '%your_keyword%'
August 24, 2007 at 8:28 am
Mark!!!
You rock bro!
Many thanks!
_________________________
August 24, 2007 at 9:06 am
An alternative for SQL Server 2005:
select object_name(object_id)
from sys.sql_modules
where definition like '%your_keyword%'
(This solution does not need the distinct, as the definitions are LOBs; also the syscomments will probably go in the future)
Regards,
Andras
August 24, 2007 at 9:26 am
Thanks Andras, that's good. I used syscomments for years and did not know that in 2005 they added something new for it.
sql_modules is certainly better, you don't have to concatenate all the lines for a procedure. Besides, it has bits for all S.P. setups.
August 24, 2007 at 10:25 am
The code is there to produce it, just change the alter to a create.
Mine is just an easy way (for me) to look trough all the user databases for code, and since it is a stored procedure sitting in master I can call it from any database and either search all databases or just the one I am currently in.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply