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)?
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.
This should do the trick:
CREATE TABLE dbo.TableA ( RowID INT, Name VARCHAR(20), StartDate DATETIME, EndDate DATETIME ) GO CREATE TABLE dbo.TableB ( RowID INT, Name VARCHAR(20), StartDate DATETIME, EndDate DATETIME ) GO CREATE VIEW dbo.ViewA AS SELECT RowID, StartDate, EndDate FROM dbo.TableA; GO CREATE VIEW dbo.ViewB AS SELECT RowID, StartDate, EndDate FROM dbo.TableB; GO CREATE PROC dbo.ProcA AS SELECT RowID, Name, StartDate FROM dbo.TableA RETURN GO CREATE PROC dbo.ProcB AS SELECT RowID, Name, StartDate FROM dbo.TableB RETURN GO SELECT o1.type_desc AS ReferencingObjectType ,OBJECT_SCHEMA_NAME(d.[object_id]) AS ReferencingSchema ,OBJECT_NAME(d.[object_id]) AS ReferencingObject ,COALESCE(COL_NAME(d.[object_id], d.column_id), '(n/a)') AS ReferencingColumn ,o2.type_desc AS ReferencedObjectType ,OBJECT_SCHEMA_NAME(d.referenced_major_id) AS ReferencedSchema ,OBJECT_NAME(d.referenced_major_id) AS ReferencedObject ,COALESCE(COL_NAME(d.referenced_major_id, d.referenced_minor_id), '(n/a)') AS ReferencedColumn ,d.class_desc ,d.is_selected ,d.is_updated ,d.is_select_all FROM sys.sql_dependencies d INNER JOIN sys.objects o1 ON o1.[object_id] = d.[object_id] INNER JOIN sys.objects o2 ON o2.[object_id] = d.referenced_major_id WHERE OBJECT_SCHEMA_NAME(referenced_major_id) = 'dbo' AND OBJECT_NAME(referenced_major_id) = 'TableA' AND COALESCE(COL_NAME(referenced_major_id, referenced_minor_id), '(n/a)') = 'StartDate' See here: https://msdn.microsoft.com/en-us/library/ms174402.aspx
There is a note that sys.sql_dependencies will be removed from a future version of SQL Server. I tried writing an equivalent query using the recommended catalog view (sys.sql_expression_dependencies), but this did not seem to provide all the table.column dependencies.
you need to use OR operator instead of AND
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%Table1%' OR Object_definition(object_id) LIKE '%Table2%' OR Object_definition(object_id) LIKE '%Table3%' OR Object_definition(object_id) LIKE '%Table4%' 