Many a times need arises to find all stored procedures related to a table in the database. The below queries come handy then.
-- Query 1 SELECT DISTINCT objects.name, objects.type, comments.text proc_defintion FROM syscomments comments INNER JOIN sys.objects objects ON comments.id=objects.object_id WHERE comments.text LIKE '%Person.Address%' AND objects.type='P'
The system table syscomments contains the definition of procedures, view, rule, default, trigger, check constraints and default constraints in a database. Thus, Querying syscomments as shown above returns the list of procedures which have a particular table in their definition. The output of above query is shown below.
Another way to get the above result is to query sys.sql_modules catalog view. The query for the same is given below
-- Query 2 select objects.name,objects.type, modules.definition from sys.sql_modules modules join sys.objects objects on objects.object_id=modules.object_id WHERE modules.definition like '%Person.Address%' and objects.type='P'
To quote MSDN “Returns a row for each object that is an SQL language-defined module in SQL Server. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view” (http://msdn.microsoft.com/en-us/library/ms175081.aspx). Thus, querying the sys.sql_modules as shown above lists all stored procedure with a specified table in their definition. It is available from SQL Server 2008.