Search for a field name in stored procedures

Hi,

Is there any way to find all stored procedures that contain a given
field
Example: I want to find all stored procedures that work with the field
ShipDate in tblOrder table

Thanks, Eugene

re: Search for a field name in stored procedures

select text
from syscomments
where text like '%shipdate%' and text like '%tblOrder%'

re: Search for a field name in stored procedures

"Eugene" <ygorelik20@hotmail.com> wrote in message
news:aded648f.0502020903.7e448787@posting.google.c om...[color=blue]
> Hi,
>
> Is there any way to find all stored procedures that contain a given
> field
> Example: I want to find all stored procedures that work with the field
> ShipDate in tblOrder table
>
> Thanks, Eugene[/color]

Check out sp_depends, but bear in mind that it isn't always reliable,
depending on the order of object creation, dynamic SQL and so on.

Simon

re: Search for a field name in stored procedures

Eugene (ygorelik20@hotmail.com) writes:[color=blue]
> Is there any way to find all stored procedures that contain a given
> field
> Example: I want to find all stored procedures that work with the field
> ShipDate in tblOrder table[/color]


select SP = o.name, tbl = o2.name, col = c.name, isupdated = d.resultobj
from sysobjects o
join sysdepends d on o.id = d.id
join sysobjects o2 on d.depid = o2.id
join syscolumns c on d.depid = c.id
and d.depnumber = c.colid
where o2.name in ('tblOrder) and c.name in( ShipDate')
order by o.name, o2.name, c.name

Note however that this information will not include references from
stored procedures that were created before tblOrder was created. Also,
references from queries that involves temp tables or other tables missing
when the procedure was created are also missing.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp