find all sps where a field is used

In my database i want list of all stored procedures where i have used a
specific field.
Is there any way to do this.. any system sp which does thisYou have to search the prcedure definition for that, because AFAIK
there is no system binding or something like this where the information
is stored which columns are used. So the approach could be SELECT
Routine_name from INFORMATION_SCHEMA.Routines Where Routine_definition
like '%SomeValue%'
HTH, jens Suessmeyer.|||The information isn't stored in any system table, except for the source code
for the procedure
(syscomments). So you can use a LIKE predicate against the text column in sy
scomments, but be
careful as syscomments can break the line (use several rows for a procedure)
in the middle of a word
(at least it used to, perhaps newer versions are better).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:OmDLgniAGHA.204@.TK2MSFTNGP15.phx.gbl...d">
> In my database i want list of all stored procedures where i have used a
> specific field.
> Is there any way to do this.. any system sp which does this
>|||This might not be very pretty, but it should do the job:
CREATE TABLE #Depends
([Name] nvarchar(128),[type]nvarchar(128))
INSERT INTO #Depends
EXEC SP_Depends 'Table1'
SELECT [Name] from #depends
WHERE [type] = 'stored procedure'
AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
LIKE '%FieldName%')
Drop Table #depends
Markus|||(The following applies to SQL 2000. I haven't tried it in SQL 2005.)
I'm assuming that enterprise manager uses sp_depends to display dependancy
information. If this is true, your suggestion will only work if objects are
created in the correct sequence.
For example, it works if:
1. Create Table1.
2. Create proc MyProc1 that select Data from Table1.
If you drop table1 and recreate it, dependancy information is lost and will
not be shown. Same holds true for stored procedures. If you create a store
d
procedure that references another stored procedure that doesn't exist yet,
you will not have dependancy information even after you create the other
stored procedure.
IMHO, the only sure fired way is to script the database to a text file and
search it for the column name and/or table names that you are interested in.
Also, if any view, stored procedure, or user-defined function uses an *,
then you can't search by column name.
Hope that helps,
Joe
"m.bohse@.quest-consultants.com" wrote:

> This might not be very pretty, but it should do the job:
> CREATE TABLE #Depends
> ([Name] nvarchar(128),[type]nvarchar(128))
> INSERT INTO #Depends
> EXEC SP_Depends 'Table1'
> SELECT [Name] from #depends
> WHERE [type] = 'stored procedure'
> AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
> LIKE '%FieldName%')
> Drop Table #depends
> Markus
>