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)?

This is a lot more complicated than it initially seems. How could you possibly know what table a certain column belongs to in this context? You might be able to look through sys.sysdepends too but if you have the same column name in more than 1 table you are going to face a serious challenge. Then you throw in the very real possibility that another columns was aliased in your query to the name you are looking for and you have yet another challenge. — Sean Lange, Feb 11 '15 at 16:56

score 1 · Answer 1 · answered Feb 11 '15 at 17:17

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.

answered Feb 11 '15 at 17:17

Nick Sandel

102
7

This is partially helpful. Like you say that column names are not usually unique (from other table columns) - that is why I was wondering if it was possible to say show only stored procedures that reference column A from Table A and Column B from Table A, etc. – djblois Feb 12 '15 at 14:10
As per http://stackoverflow.com/questions/14704105/search-text-in-stored-procedure-in-sql-server you could instead try searching the text of your stored procedures for a TableName.ColumnName reference in the text. Just be careful with that for [TableName].[ColumnName] type references. – Nick Sandel Feb 12 '15 at 14:24

score 0 · Answer 2 · answered Jan 22 '16 at 15:44

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.

score 0 · Answer 3 · answered May 30 '18 at 05:58

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%'

Find Stored Procedures that Reference specific columns

3 Answers3