Find Stored Procedures that Reference specific columns

Question

I found this code that will find any stored Procedures that reference a single specific column. Now I would like to create one that finds a stored procedure that references more than one specific column.

SELECT DISTINCT Name 
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_name%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%';

also if possible can I specify sometimes what table the columns may come from (sometimes multiple tables)?

Answer 1

Surely just expand your SQL to include additional AND clauses like:

SELECT DISTINCT Name 
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_name%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Other_tbl_name%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Other_CreatedDate%';

Would that work for you?

Also unless your column names are unique per table I expect you could get false positives so for example tbl_name might be referenced as may a CreatedDate column but that doesn't mean the column reference to CreatedDate is from tbl_name.CreatedDate.

Also I don't think this will catch any references in dynamic SQL as references in there are just text and can't be bound to system objects.