How to Identify All Stored Procedures Referring a Particular Table

How to identify all stored procedures referring a particular table

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ

How to identify all stored procedures refering a particular table in Oracle

Use the ALL_DEPENDENCIES dictionary table:

SELECT *
FROM   ALL_DEPENDENCIES
WHERE  referenced_name = 'YOUR_TABLE_NAME'
AND    owner           = 'YOUR_USER';

Find the stored procedure which uses a particular table in sql server

The following query maybe of help to you.

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS S
INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.TEXT LIKE '%Table_name%'
    AND O.type = 'P'

It can search if a particular word is contained in the stored procedure. If a table name is what you need to find, then it can find it from the stored procedure text. Hope it helps.

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

How to find all Stored Procedures that referring particular Stored Procedure

In the following query, in the WHERE clause, instead of '%YourProcedureName%', you can replace your procedure name, it will return the other procedures which has the given procedure name in it.

-- Find specific word in the SPs
SELECT DISTINCT P.[name], M.[definition]
FROM sys.procedures P
JOIN sys.sql_modules M ON M.[object_id] = P.[object_id]
WHERE M.[definition] LIKE '%YourProcedureName%' 

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

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

---

Related Topics