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

You 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 syscomments, 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).

Show quoteHide quote

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 stored
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

Show quoteHide quote

Help with this trigger
Sum Total Hours and Minutes in a report
Aggregate Function Error
Joining two tables multiple times
please check