August 18, 2003 at 10:20 am
I would like to search all stored procedures for a given string on a given database.
I have a list of table names and would like to know what stored procedures access the tables.
Any ideas?
August 18, 2003 at 11:14 am
select top 100 so.name, sc.text
from sysobjects so
inner join syscomments sc
on so.id = sc.id
where so.xtype = 'P'
and patindex('%TableName%', convert(varchar(8000), sc.text)) > 0
August 18, 2003 at 12:20 pm
Thanks Jpipes, it worked great!
August 19, 2003 at 8:19 am
here's the one I use to find any string in any SP or VIEW.
Change the word 'PROC' below to 'VIEW' if you want to search views instead
select s.name, c.* from syscomments as c
join sysobjects s on s.id = c.id
where charindex(upper('StringImLookingFor'),upper(c.text)) >0
and charindex(upper('proc'),upper(c.text)) >0
August 19, 2003 at 8:24 am
Thank you, bar8393. I will add this to my archives.
quote:
here's the one I use to find any string in any SP or VIEW.Change the word 'PROC' below to 'VIEW' if you want to search views instead
select s.name, c.* from syscomments as c
join sysobjects s on s.id = c.id
where charindex(upper('StringImLookingFor'),upper(c.text)) >0
and charindex(upper('proc'),upper(c.text)) >0
August 22, 2003 at 8:58 am
quote:
Thank you, bar8393. I will add this to my archives.quote:
here's the one I use to find any string in any SP or VIEW.Change the word 'PROC' below to 'VIEW' if you want to search views instead
select s.name, c.* from syscomments as c
join sysobjects s on s.id = c.id
where charindex(upper('StringImLookingFor'),upper(c.text)) >0
and charindex(upper('proc'),upper(c.text)) >0
I tried it on Pubs database on a SQL7.0 box, it returned no results. Please advice.
Thanks
JX
August 22, 2003 at 9:51 am
quote:
Thanks Jpipes, it worked great!
BTW, this is exactly what I do as well, but it is not absolutely guaranteed to find all occurrences, for the following reason.
Stored procedure text is stored in syscomments in chunks of 8000 bytes. Therefore if you have a reasonably long SP, it is just possible that the string you're searching for lies across the boundary between 2 records and will not therefore be found.
Very minor point this, and you'd be pretty unlucky for this to happen, but it's just worth bearing in mind.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply