sqlsql-serverselectfind

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


I have a value in a table that was changed unexpectedly. The column in question is CreatedDate, which 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?


Solution

  • 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