Searching all user stored procedures for a table name
Thuy Pham
inside each stored procedure to look for a string or table name,is there
a way of listing all user stored procedures that contained that string
or table name?
Thanks,
Thuy Pham
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
John Bell
You could script them in enterprise manager and search the resulting
file or check the text in syscomments.
John
adpx
sp_helptext on a stored procedure returns a resultset of the sp's text.
Consider running insert exec of that over all your sps, writing to a temp
table, and then searching the table.
--A
Thuy Pham
Thanks John. I tried your way and it works. I generated SQL script of
all store procedures and then use query analyzer to search for the
string.
Bruce
I use it as a find that table/field/column for my
stored procs & views
/****** Object: Stored Procedure dbo.sp_grep Script Date:
1/25/2002 9:12:13 AM ******/
if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp_grep]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[sp_grep]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.sp_grep Script Date:
1/25/2002 9:12:13 AM ******/
create proc sp_grep
-- Search the source code of all objects for a string or combination
of strings
-- Versions: 1.0 - 03/16/1995, 1.1 - 10/26/1995, 7.0.0 - 07/24/1999
-- Author: Andrew Zanevsky, AZ Databases, Inc.
-- zane...@azdatabases.com
@parameter nvarchar(4000) = null,
@case char(1) = 's'
as
set nocount on
declare @str_no smallint,
@str_no_char varchar(5),
@operation char(1),
@string nvarchar(1000),
@oper_pos smallint,
@context nvarchar(4000),
@i smallint,
@longest tinyint,
@len_name char(5),
@len_owner char(5),
@len_type char(5)
if @parameter is null -- provide instructions
begin
print 'Execute sp_grep
"{string1}operation1{string2}operation2{string3}...", [case]'
print '- stringN is a string of up to 1000 characters, enclosed in
curly brackets.'
print ' Brackets may be omitted if stringN does not contain
leading and trailing'
print ' spaces or characters: +,-,&.'
print '- operationN is one of the characters: +,-,&. Interpreted
as or,minus,and.'
print ' Operations are executed from left to right with no
priorities.'
print '- case: specify "i" for case insensitive comparison.'
print 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
print ' will search for all objects that have an occurence of
string "alpha"'
print ' or string "beta gamma", do not have string "delta", '
print ' and have string "+++".'
return
end
-- Check for <CarriageReturn> or <LineFeed> characters
if charindex( char(10), @parameter ) > 0 or charindex( char(13),
@parameter ) > 0
begin
print 'Parameter string may not contain <CarriageReturn> or
<LineFeed> characters.'
return
end
if lower( @case ) = 'i'
select @parameter = lower( ltrim( rtrim( @parameter ) ) )
else
select @parameter = ltrim( rtrim( @parameter ) )
create table #search ( str_no smallint, operation char(1), string
nvarchar(1000), last_obj int )
create table #found_objects ( id int, str_no tinyint )
create table #result ( id int )
-- Parse the parameter string
select @str_no = 0
while datalength( @parameter )/2 > 0
begin
-- Get operation
select @str_no = @str_no + 1, @str_no_char = rtrim( convert(
char(5), @str_no + 1 ) )
if @str_no = 1
select @operation = '+'
else
begin
if substring( @parameter, 1, 1 ) in ( '+', '-', '&' )
select @operation = substring( @parameter, 1, 1 ),
@parameter = ltrim( right( @parameter, datalength(
@parameter )/2 - 1 ) )
else
begin
select @context = rtrim( substring(
@parameter + space( 4000 - datalength(
@parameter )/2 ), 1, 20 ) )
print N'Incorrect or missing operation sign before "' +
@context + N'".'
print N'Search string ' + @str_no_char + N'.'
return
end
end
-- Get string
if datalength( @parameter ) = 0
begin
print 'Missing search string at the end of the parameter.'
print 'Search string ' + @str_no_char + '.'
return
end
if substring( @parameter, 1, 1 ) = '{'
begin
if charindex( '}', @parameter ) = 0
begin
select @context = rtrim( substring(
@parameter + space( 4000 - datalength(
@parameter )/2 ), 1, 200 ) )
print N'Bracket not closed after "' + @context + N'".'
print N'Search string ' + @str_no_char + N'.'
return
end
if charindex( '}', @parameter ) > 1002
begin
select @context = rtrim( substring(
@parameter + space( 4000 - datalength(
@parameter )/2 ), 2, 20 ) )
print N'Search string ' + @str_no_char + N' is longer than
1000 characters.'
print N'String begins with "' + @context + N'".'
return
end
select @string = substring( @parameter, 2, charindex( '}',
@parameter ) - 2 ),
@parameter = ltrim( right( @parameter,
datalength( @parameter )/2 - charindex(
'}', @parameter ) ) )
end
else
begin
-- Find the first operation sign
select @oper_pos = datalength( @parameter )/2 + 1
if charindex( '+', @parameter ) between 1 and @oper_pos
select @oper_pos = charindex( '+', @parameter )
if charindex( '-', @parameter ) between 1 and @oper_pos
select @oper_pos = charindex( '-', @parameter )
if charindex( '&', @parameter ) between 1 and @oper_pos
select @oper_pos = charindex( '&', @parameter )
if @oper_pos = 1
begin
select @context = rtrim( substring(
@parameter + space( 4000 - datalength(
@parameter )/2 ), 1, 20 ) )
print N'Search string ' + @str_no_char + N' is missing,
before "' + @context + N'".'
return
end
if @oper_pos > 1001
begin
select @context = rtrim( substring(
@parameter + space( 4000 - datalength(
@parameter )/2 ), 1, 20 ) )
print N'Search string ' + @str_no_char + N' is longer than
1000 characters.'
print N'String begins with "' + @context + N'".'
return
end
select @string = substring( @parameter, 1, @oper_pos - 1 ),
@parameter = ltrim( right( @parameter,
datalength( @parameter )/2 - @oper_pos +
1 ) )
end
insert #search values ( @str_no, @operation, @string, 0 )
end
select @longest = max( datalength( string )/2 ) - 1
from #search
------------------------------------------------------------------------
-- Search for strings
if @case = 'i'
begin
insert #found_objects
select a.id, c.str_no
from syscomments a, #search c
where charindex( c.string, lower( a.text ) ) > 0
insert #found_objects
select a.id, c.str_no
from syscomments a, syscomments b, #search c
where a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and charindex( c.string,
lower( right( a.text, @longest ) +
substring( b.text, 1, @longest ) ) ) > 0
end
else
begin
insert #found_objects
select a.id, c.str_no
from syscomments a, #search c
where charindex( c.string, a.text ) > 0
insert #found_objects
select a.id, c.str_no
from syscomments a, syscomments b, #search c
where a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and charindex( c.string,
right( a.text, @longest ) +
substring( b.text, 1, @longest ) ) > 0
end
------------------------------------------------------------------------
select distinct str_no, id into #dist_objects from #found_objects
create unique clustered index obj on #dist_objects ( str_no, id )
-- Apply one operation at a time
select @i = 0
while @i < @str_no
begin
select @i = @i + 1
select @operation = operation from #search where str_no = @i
if @operation = '+'
insert #result
select id
from #dist_objects
where str_no = @i
else if @operation = '-'
delete #result
from #result a, #dist_objects b
where b.str_no = @i
and a.id = b.id
else if @operation = '&'
delete #result
where not exists
( select 1
from #dist_objects b
where b.str_no = @i
and b.id = #result.id )
end
-- Select results
select distinct id into #dist_result from #result
select @len_name = str( isnull( max( datalength( rtrim( o.name ) )/2
), 5 ), 5 ),
@len_owner = str( isnull( max( datalength( rtrim(
user_name(uid) ) )/2 ), 5 ), 5 ),
@len_type = str( isnull( max( datalength( rtrim(
substring(v.name,5,31) ) )/2 ), 5 ), 5 )
from #dist_result d,
sysobjects o,
master.dbo.spt_values v
where d.id = o.id
and o.xtype = substring(v.name,1,2)
and v.type = 'O9T'
if @@rowcount = 0 print 'No objects found using the given search
criteria'
else exec('
select "Name" = substring( o.name, 1, ' + @len_name + ' ),
"Owner" = substring( user_name(uid), 1, ' +
@len_owner + ' ),
"Object_type" = substring( substring(v.name,5,31), 1, ' +
@len_type + ' )
from #dist_result d,
sysobjects o,
master.dbo.spt_values v
where d.id = o.id
and o.xtype = substring(v.name,1,2)
and v.type = "O9T"
order by Object_type desc, Name asc
')
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO