Find In...Stored Procedures?

10 Sep 2005 1:10 PM

jpuopolo

All:

Is there a way to do a "Find" across the text of a set of stored
procedures? (SQL Server 2000)

I am in the process of doing some code refactoring and have eliminated
a column in one of my tables. Now, I want to find all the stored
procedures that use the column name.

Is there a way to do this? Alternatives?

Many thanks,
John

10 Sep 2005 1:26 PM

Alejandro Mesa

How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037

AMB

Show quote

"jpuop***@mvisiontechnology.com" wrote:

> All:
>
> Is there a way to do a "Find" across the text of a set of stored
> procedures? (SQL Server 2000)
>
> I am in the process of doing some code refactoring and have eliminated
> a column in one of my tables. Now, I want to find all the stored
> procedures that use the column name.
>
> Is there a way to do this? Alternatives?
>
> Many thanks,
> John
>
>

10 Sep 2005 1:41 PM

Stu

Look at the syscomments table; it contains the text for your stored
procedures and views.

SLECT *
FROM syscomments
WHERE text like '%[column name]%'

HTH,
Stu

10 Sep 2005 1:45 PM

jpuopolo

Stu:

Thanks - worked like a charm.

Best,
John

10 Sep 2005 5:13 PM

Hari Prasad

Use dbname
go
SELECT OBJECT_NAME(ID) from syscomments
where TEXT like '%columnname%'

Thanks
Hari
SQL Server MVP

Show quote

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1126359684.486000.4260@g49g2000cwa.googlegroups.com...
> Look at the syscomments table; it contains the text for your stored
> procedures and views.
>
> SLECT *
> FROM syscomments
> WHERE text like '%[column name]%'
>
> HTH,
> Stu
>