CODEWITHSUNDEEP
sqlsql-server
Md. Tarikul Islam Soikot
Md. Tarikul Islam Soikot

Reputation: 595

How to find out which stored procedure is using the specific column of a specific table in SQL Server?

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.

Upvotes: 3

Views: 3157

Answers (4)

SQLpro
SQLpro

Reputation: 5131

Due to the dynamic aspect of SQL (links between objects are not always known at the moment of creating the object except in the case of using SCHEMABINDING directive) links are completed when "executing" the object. So the only way to be shure that you will find all objetc with a reference of another object or attribute of an object is to use an internal standard for naming...

Personnaly I use this convention for my business development (actually I do that for clubmed...)... But it is in french !

To summarize:

  1. All tables have a name beginning by T_ and ending with a unique trigram (unique in the table list). Example : T_CUSTOMER_CST
  2. All views have a name beginning by V_ and ending with a unique trigram (unique in the view list). Example : V_CUSTOMER_CST
  3. All table function have a name beginning by F_ and ending with a unique trigram (unique in the function list). Example : F_CUSTOMER_CST
  4. All internal data colums of an table have a name beginning with the trigram of the table. Example for table T_CUSTOMER_CST the name of a customer is CST_NAME, the ID is CST_ID.
  5. All external data columns (foreign key) keep the original name, and if it exists more than one reference, be completed by some distinguishing element. Example for table T_JOURNEY_JRN the id of the origin town an the target town comming from table T_TOWN_TWN are TWN_ID_STARTS and TWN_ID_ENDS

And so on...

So to find any impact of changing the definition of a column in routines (procs, triggers, functions, view...) the query to run is :

SELECT *
FROM   sys.sql_modules
WHERE  definition LIKE '%TWN?_ID%' ESCAPE '?'

This will find all object containing a column nammed TWN_ID.

Upvotes: 0

FernandoHidalgo
FernandoHidalgo

Reputation: 1

The table sys.dm_sql_referenced_entities will give you the information that you are after. You can check its reference in the Microsoft website: SQL Server 2019 Reference

I like to complete this with a text search. To do this you can export all your Store Procedures scripts as flat files and then you search in the files.

You can use the function "Find in Files" of Notepad++ to find references. You will need to consider that column may have been referred using different formats: ."Date_Id", .[Date_Id] or .Date_Id.

Upvotes: 0

Charlieface
Charlieface

Reputation: 71596

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

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453327

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

Upvotes: 2

Related Questions