ansaurus

Question

Answer 1

+3 A:

select OBJECT_NAME(object_id) 
from sys.sql_modules
where  OBJECTPROPERTY(object_id,'IsProcedure') = 1 and 
    definition like '%StudentId%'

Martin Smith 2010-09-27 14:03:06

This won't work (i.e., be reliable) if the SP is using a view or UDF that references `StudentId`, though.

RedFilter 2010-09-27 14:04:40

@RedFilter - As long as they are not using `*`. It will.

Martin Smith 2010-09-27 14:06:33

thanks for inputs.that helped :-)

Romil Nagrani 2010-09-27 14:06:33

@Martin: not necessarily - `StudentId` may be used in the `JOIN` or `WHERE` clauses, not in the `SELECT` clause.

RedFilter 2010-09-27 14:08:29

@RedFilter - See what you mean. @Romil - You would just remove the `IsProcedure` filter to find those.

Martin Smith 2010-09-27 14:10:24

Retrieve List of all SPs with some condition