Pomster Pomster - 4 months ago 18
SQL Question

Find all stored procedures that reference a column in table "someTable"

I have a value in a table that was changed unexpectedly, The value in question is CreatedDate, this is set when my item is created, but has been getting change by a stored proc.

Could i write some type of select statement to get all the proc names the reference this column my my table?

Answer

One option is to create a script file.

Right click on the database -> Tasks -> Generete 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