Find all stored procedures that reference a specific column in some table

Question

I have a value in a table that was changed unexpectedly  The column in question is CreatedDate  this is set when my item is created  but it s being changed by a stored procedure   Could I write some type of SELECT statement to get all the procedure names that reference this column from my table

User · Answer

SELECT   FROM   sys all sql modules WHERE  definition LIKE   CreatedDate

User · Answer

You can use ApexSQL Search  it s a free SSMS and Visual Studio add-in and it can list all objects that reference a specific table column  It can also find data stored in tables and views  You can easily filter the results to show a specific database object type that references the column    Disclaimer  I work for ApexSQL as a Support Engineer

User · Answer

If you want to get stored procedures using specific column only  you can use try this query      SELECT DISTINCT Name FROM sys Procedures WHERE object definition OBJECT ID  LIKE   CreatedDate      If you want to get stored procedures using specific column of table  you can use below query    SELECT DISTINCT Name  FROM sys procedures WHERE OBJECT DEFINITION OBJECT ID  LIKE   tbl name   AND OBJECT DEFINITION OBJECT ID  LIKE   CreatedDate

User · Answer

You can use the below query to identify the values  But please keep in mind that this will not give you the results from encrypted stored procedure    SELECT DISTINCT OBJECT NAME comments id  OBJECT NAME      objects type desc FROM syscomments comments      sys objects objects WHERE comments id   objects object id     AND TEXT LIKE   CreatedDate   ORDER BY 1

User · Answer

-- Search in All Objects  SELECT OBJECT NAME OBJECT ID   definition FROM sys sql modules WHERE definition LIKE        ColumnName        GO   -- Search in Stored Procedure Only  SELECT DISTINCT OBJECT NAME OBJECT ID  FROM sys Procedures WHERE object definition OBJECT ID  LIKE        ColumnName        GO

User · Answer

One option is to create a script file   Right click on the database -  Tasks -  Generate Scripts  Then you can select all the stored procedures and generate the script with all the sps  So you can find the reference from there   Or  -- Search in All Objects SELECT OBJECT NAME OBJECT ID   definition FROM sys sql modules WHERE definition LIKE        CreatedDate        GO  -- Search in Stored Procedure Only SELECT DISTINCT OBJECT NAME OBJECT ID   object definition OBJECT ID  FROM sys Procedures WHERE object definition OBJECT ID  LIKE        CreatedDate        GO   Source SQL SERVER     Find Column Used in Stored Procedure     Search Stored Procedure for Column Name

User · Answer

try this    SELECT Name FROM sys procedures WHERE OBJECT DEFINITION OBJECT ID  LIKE   CreatedDate   GO   or you can generate a scripts of all procedures and search from there

User · Answer

i had the same problem and i found that Microsoft has a systable that shows dependencies   SELECT      referenced id       referenced entity name AS table name       referenced minor name as column name       is all columns found FROM sys dm sql referenced entities   dbo Proc1    OBJECT       And this works with both Views and Triggers

User · Answer

You can use the system views contained in information schema to search in tables  views and  unencrypted  stored procedures with one script  I developed such a script some time ago because I needed to search for field names everywhere in the database  The script below first lists the tables views containing the column name you re searching for  and then the stored procedures source code where the column is found  It displays the result in one table distinguishing  quot BASE TABLE quot    quot VIEW quot  and  quot PROCEDURE quot   and  optionally  the source code in a second table  DECLARE  SearchFor nvarchar max    CustomerID   -- search for this string DECLARE  SearchSP bit   1 -- 1 search in SPs as well DECLARE  DisplaySPSource bit   1 -- 1 display SP source code  -- tables if   SearchSP 1  begin         select     c table Schema       c table Name       c column name      schema object                t table type    from information schema columns c   left join information schema Tables t on c table name t table name   where column name like  SearchFor    union   select     routine Schema       routine Name      schema object              PROCEDURE  as table type from information schema routines   where routine definition like  SearchFor         and routine type  procedure        order by table type  schema object end else begin   select     c table Schema       c table Name       c column name      schema object             t table type    from information schema columns c   left join information schema Tables t on c table name t table name   where column name like  SearchFor    order by c table Name  c column name end      -- stored procedure  source listing  if   SearchSP 1  begin           if   DisplaySPSource 1  begin       select     routine Schema       routine Name      schema sp   routine definition        from information schema routines       where routine definition like  SearchFor       and routine type  procedure        order by routine name     end end  If you run the query  use the  quot result as text quot  option - then you can use  quot find quot  to locate the search text in the result set  useful for long source code   Note that you can set  DisplaySPSource to 0 if you just want to display the SP names  and if you re just looking for tables views  but not for SPs  you can set  SearchSP to 0  Example result  find CustomerID in the Northwind database  results displayed via LinqPad    Note that I ve verfied this script with a test view dbo TestOrders and it found the CustomerID in this view even though c   was used in the SELECT statement  referenced table Customers contains the CustomerIDand hence the view is showing this column    Note for LinqPad users  In C   you can use dc ExecuteQueryDynamic sqlQueryStr  new object        parameters        Dump    and have the parameters as  p0      pn inside the query string  Then you can write a static extension class and save it under My Extensions to be used in your LinqPad queries  The data context can be passed from the query window as DataContextBase dc via parameter  i e  public static void SearchDialog this DataContextBase dc  string searchString    quot   quot   inside a public static extension class  in LinqPad 6  it is DataContext   Then you can rewrite the SQL query above as a string with parameters and invoke it from the C  context

[sql] Find all stored procedures that reference a specific column in some table

Examples related to sql

Examples related to sql-server

Examples related to select

Examples related to find

Examples related to columnname