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]

Re: Search for a field name in stored procedures

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

[Simon Hayes]

Re: Search for a field name in stored procedures


"Eugene" <ygorelik20@hot mail.com> wrote in message
news:aded648f.0 502020903.7e448 787@posting.goo gle.com...[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

[Erland Sommarskog]

Re: Search for a field name in stored procedures

Eugene (ygorelik20@hot mail.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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp