Search for a field name in stored procedures

[Eugene]

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

[louis]

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

[Simon Hayes]

"Eugene" <yg********@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...

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

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

[Erland Sommarskog]

Eugene (yg********@hotmail.com) writes:

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

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, es****@sommarskog.se

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