use master
go
-- =============================================
-- Description: Search a text in stored procedure source code.
-- @text - any text to find, search is done by like '%text%'
-- @dbname - database where to search,
-- - if omitted, all databases in the SQL server instance
-- =============================================
alter PROCEDURE [dbo].[sp_FindTextInsideProcedure]
@text varchar(250),
@dbname varchar(64) = null ,
@OrderByColumn int = 1 ,
@Level int = null
AS BEGIN
SET NOCOUNT ON;
if ( @dbname is null or @dbname like '%[_]%' or @dbname like '%[%]%' ) and @Level is null
begin
/*
-- cursor programming
--enumerate all databases.
DECLARE #db CURSOR FOR Select Name from master..sysdatabases where ( @dbname is null or Name like @dbname )
declare @c_dbname varchar(64)
OPEN #db FETCH #db INTO @c_dbname
while @@FETCH_STATUS <> -1 --and @MyCount < 500
begin
execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname
FETCH #db INTO @c_dbname
end
CLOSE #db DEALLOCATE #db
*/
-- no cursor
--enumerate all databases.
Select Name
into #db
from master..sysdatabases
where ( @dbname is null or Name like @dbname )
declare @c_dbname varchar(64)
set @c_dbname = (select top 1 Name from #db)
declare @result table ( db varchar(64) , name varchar(256) , [definition] nvarchar(max))
while ( @c_dbname is not null )
begin
insert into @result
execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname , @Level =1
delete #db
where name = @c_dbname
-- get the next database name
set @c_dbname = (select top 1 Name from #db)
end
drop table #db
select * from @result r
order by case @OrderByColumn
when 1 then r.db
when 2 then r.name
when 3 then r.[definition]
else r.db
end
end --if @dbname is null
else
begin --@dbname is not null
declare @sql varchar(4000)
--create the find like command
select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '
+ ' from ['+@dbname+'].sys.sql_modules m '
+ ' inner join ['+@dbname+']..sysobjects o '
+ ' on m.object_id=o.id'
+ ' where [definition] like ''%'+@text+'%'''
if (@Level is null)
begin
select @sql = @sql + 'order by case ' + convert(varchar , @OrderByColumn )
+ ' when 1 then ''' + @dbname + ''''
+ ' when 2 then o.name '
+ ' when 3 then m.definition '
+ ' else ''' + @dbname + ''''
+ ' end'
end
execute (@sql)
end --@dbname is not null
END
