By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Search for a field name in stored procedures

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
select text
from syscomments
where text like '%shipdate%' and text like '%tblOrder%'

Jul 23 '05 #2

P: n/a

"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
Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.