CODEWITHSUNDEEP
sqlsql-serverstored-procedures
henryloke
henryloke

Reputation: 81

How to check all stored procedures which update a specific column

Following is the sample to search stored procedures which have the keyword Student in them:

SELECT TOP (1000) *
FROM [eUniversityManagement].[sys].[all_sql_modules]
WHERE definition LIKE '%Student%'

I just wondering can we search any stored procedure that has and UPDATE on Student.name for example:

UPDATE STUDENT 
SET Name = @Name, 
    Address = @Address, 
    DOB = @DOB 
WHERE id = @StudentID

Thanks.

Upvotes: 1

Views: 1733

Answers (1)

Charlieface
Charlieface

Reputation: 72501

You can use the sys.dm_sql_referenced_entities function for this. It's a little complicated, as it wants the procedure name which references the column, so you need to start off with all objects and apply the functions

SELECT o.name, schema_name = s.name
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
CROSS APPLY sys.dm_sql_referenced_entities (QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT') r
WHERE o.type IN ('P', 'TR')
  AND r.referenced_entity_name = 'student'
  AND r.referenced_minor_name = 'Name'
  AND r.is_updated = 1;

Upvotes: 3

Related Questions