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