(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 20-------------------------------------------------- Find all sp's using a particular table column By: Yasub_exceldbp Date: March 7, 2014, 6:23 am --------------------------------------------------------- Hey All!!! Excerpts from one of my interactions with .NET Developers: " Developer: Hey Me: Hey Developer: I need one help Me: Sure Developer: Can you get me a list of sp's referring the column "CustomerName" of the "tblCustomers" table in the "Sales" Database? " Hmmmm... Now I am sure if i ask 10 DB Developers, that has the above happened to you, at least 9 hands would be raised! :) Here's a very handy script, in SQL 2005 and above, to figure out the dependency: ---------------------------------------------------------------- ----------------------- SELECT OBJECT_NAME(A.Object_id) SP ,OBJECT_NAME(A.Referenced_Major_Id) DependsTable ,B.Name DependsColumns ,Action = CASE WHEN A.Is_Selected = 1 THEN 'SELECT' WHEN A.Is_Updated = 1 THEN 'INSERT/UPDATE' WHEN A.Is_Select_All = 1 THEN 'SELECT ALL' END FROM sys.sql_dependencies A INNER JOIN sys.Columns B ON A.Referenced_Minor_Id = B.Column_ID AND A.Referenced_Major_Id = B.Object_ID WHERE CASE WHEN A.Is_Selected = 1 THEN 'SELECT' WHEN A.Is_Updated = 1 THEN 'INSERT/UPDATE' WHEN A.Is_Select_All = 1 THEN 'SELECT ALL' END IS NOT NULL AND OBJECT_NAME(A.Referenced_Major_Id) NOT IN ('sysdiagrams') and B.Name like '%CustomerName%' AND OBJECT_NAME(A.Referenced_Major_Id) like '%tblCustomers%' ORDER BY SP,DependsTable,B.Column_ID ---------------------------------------------------------------- ----------------------- NOW, PLEASE BE WARNED... THE ABOVE SCRIPT DOES NOT WORK FOR DYNAMIC SP's. Else it works like a charm! :) Another dirty alternative: ---------------------------------------------------------------- ----------------------- SELECT DISTINCT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules WHERE Definition LIKE '%CustomerName%' AND Definition LIKE '%tblCustomers%' ---------------------------------------------------------------- ----------------------- Why is this dirty? Well, it returns sp's having a commented reference of the above objects as well. :( Both methods have their own pros and cons but are extremely handy nonetheless! Hope this has been useful. Please post your comments, if you have any suggestions or in case you have better and more efficient methods that you have been using. Thanks! -Yasub :) *****************************************************