DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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 :)
       *****************************************************