CODEWITHSUNDEEP
sqlsql-serverstored-procedures
djblois
djblois

Reputation: 835

Find Stored Procedures that Reference specific columns

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

Upvotes: 5

Views: 6124

Answers (3)

NT-Hero
NT-Hero

Reputation: 77

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

Upvotes: 0

campbean68
campbean68

Reputation: 51

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.

Upvotes: 0

Nick Sandel
Nick Sandel

Reputation: 112

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.

Upvotes: 1

Related Questions