Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!
Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?
…and that inspired me to write this post.
We spend some time researching system tables and here is the findings to find list of tables used in a procedure.
;WITH procs AS ( SELECT o1.name AS proc_name, o2.name AS table_name, ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row FROM sysdepends d INNER JOIN sysobjects o1 ON o1.id=d.id INNER JOIN sysobjects o2 ON o2.id=d.depid WHERE o1.xtype = 'P' --AND o2.name = 'tabname1' OR o2.name = 'tblname2' ) SELECT proc_name, table_name FROM procs WHERE row = 1 ORDER BY proc_name, table_name
Let me know if you have any better idea for this.