How to find out which stored procedure is using the specific column of a specific table in SQL Serve-CodePudding

Suppose I have two tables:

CREATE TABLE Customers 
(
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    FullName varchar(100) NOT NULL,
);

CREATE TABLE Employees 
(
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    FullName varchar(100) NOT NULL,
);

If I have used the FullName column of the Customers table in any stored procedure, then I want to get all those stored procedure names by searching with ColumnName = 'FullName' and TableName = 'Customers'.

I don't need those stored procedure names where I have used the FullName column from the Employees table.

Is it possible to write a script for this in SQL Server?

NB: In my search criteria I want to search with column name and Table name.

CodePudding user response：

You don't need to use sys.sql_dependencies which has been deprecated, you can use the newer sys.dm_sql_referenced_entities instead.

Unfortunately, after some testing, it turns out that sys.sql_expression_dependencies and sys.dm_sql_referencing_entities do not return information on column-level dependencies.

SELECT OBJECT_SCHEMA_NAME(o.object_id),
       OBJECT_NAME(o.object_id)
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') d
INNER JOIN sys.columns c
    ON c.object_id = d.referenced_id
    AND c.column_id = d.referenced_minor_id
    AND c.object_id = OBJECT_ID('dbo.Customers')
    AND c.name = 'FullName'
WHERE o.type IN ('FN','TF','IF','P','V','TR');  -- scalar, multi-line and inline funcs, procs, views, triggers

CodePudding user response：

sys.sql_dependencies is deprecated but whilst it is still around you can use

SELECT OBJECT_SCHEMA_NAME(d.object_id),
       OBJECT_NAME(d.object_id)
FROM   sys.sql_dependencies d
       INNER JOIN sys.columns c
               ON c.object_id = d.referenced_major_id
                  AND c.column_id = d.referenced_minor_id
WHERE  d.class = 0
       AND d.referenced_major_id = object_id('dbo.Customers')
       AND c.name = 'FullName'

This has a lot of advantages over a text search in that you won't get issues where procedures contain both the string Customers and FullName but as comments or some other context that would yield a false positive. It also handles cases where procedures contain * rather than mentioning the name explicitly.

If you use dynamic SQL you will likely need to fall back to a more painful text search though.

I'd probably end up using a hybrid approach to cover all angles as below so I can eliminate the need to actually review the text of definite dependencies found in sys.sql_dependencies and just have to manually review the "Possibles"

WITH Candidates AS
(
SELECT d.object_id, 0 AS Source
FROM   sys.sql_dependencies d
       INNER JOIN sys.columns c
               ON c.object_id = d.referenced_major_id
                  AND c.column_id = d.referenced_minor_id
WHERE  d.class = 0
       AND d.referenced_major_id = object_id('dbo.Customers')
       AND c.name = 'FullName'
UNION ALL
SELECT m.object_id, 1 AS Source
FROM sys.sql_modules m
WHERE m.definition LIKE '%Customers%' AND (m.definition LIKE '%FullName%' OR m.definition LIKE '%*%')
)
SELECT SchemaName = OBJECT_SCHEMA_NAME(object_id),
       ModuleName = OBJECT_NAME(object_id),
       Confidence = IIF(MIN(Source) = 0, 'High', 'Possible')
FROM Candidates
GROUP BY object_id