Find text in sp

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[find_text_in_sp]    Script Date: 11/23/2011 08:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[find_text_in_sp]
@text varchar(250),
@dbname varchar(64) = null,
@dbNamePrefix bit = 0
AS BEGIN
SET NOCOUNT ON;

if @dbname is null
begin
–enumerate all databases.
DECLARE #db CURSOR FOR Select Name from master..sysdatabases
declare @c_dbname varchar(64)

OPEN #db FETCH #db INTO @c_dbname
while @@FETCH_STATUS <> -1 –and @MyCount < 500
begin
execute find_text_in_sp @text, @c_dbname
FETCH #db INTO @c_dbname
end
CLOSE #db DEALLOCATE #db
end –if @dbname is null
else
begin –@dbname is not null
declare @sql varchar(250)
if @dbNamePrefix =0
BEGIN
–create the find like command
select @sql = ‘select ”’ + @dbname + ”’ as db, o.name,m.definition ‘
select @sql = @sql + ‘ from ‘+@dbname+’.sys.sql_modules m ‘
select @sql = @sql + ‘ inner join ‘+@dbname+’..sysobjects o on m.object_id=o.id’
–select @sql = @sql + ‘ where [definition] like ”%’+@text+’%”’
select @sql = @sql + ‘ where [definition] like ”%’+REPLACE(@text,””,”””)+’%”’
execute (@sql)
END
ELSE
BEGIN
–create the find like command
select @sql = ‘select ”’ + @dbname + ”’ as db, o.name,m.definition ‘
select @sql = @sql + ‘ from ‘+@dbname+’.sys.sql_modules m ‘
select @sql = @sql + ‘ inner join ‘+@dbname+’..sysobjects o on m.object_id=o.id’
–select @sql = @sql + ‘ where [definition] like ”%’+@text+’%”’
select @sql = @sql + ‘ where [definition] like ”%’+REPLACE(@text,””,”””)+’%”’
execute (@sql)
END
end –@dbname is not null
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s