2013/07/28

Find Stored Procedures and Functions That Reference Certain Tables

During the data discovery process on a database that I have inherited from another designer, I often have the need to determine all the stored procedures that interact with a certain table. For instance, I may need to look up all the stored procedures that insert records into a certain table. Or all the stored procedures that look up a certain view. Here is a code snippet that allows me to plug in the name of the table/view, and returns all the code objects (stored procedures, functions, triggers etc) that interact with that table/view. It returns the object type, the name of the object, and the code within that object broken up into more than one Code Sequence if the code is lengthy enough (longer than 4000 characters). You could, of course, also plug in the name of a code object to return other code objects that look up your searched object, for instance to find all the stored procedures that use a certain function.

Just replace the "search_term" in the code snippet with the name of the table/view/stored procedure/function etc that you want to search on.

select distinct
ObjectType = o.type_desc
, ObjectName = o.name
, CodeSequence = c_display.colid
, Code = c_display.[text]
from sys.objects o
inner join sys.syscomments c_search
on c_search.id = o.[object_id]
inner join sys.syscomments c_display
on c_display.id = o.[object_id]
where o.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
and c_search.[text] like '%search_term%'
order by 1, 2, 3

No comments:

Post a Comment