How to identify all stored procedures referring to a particular table

Begin your sys-tematic search

Diving into system views

Dive deeper into SQL Server's incorporated system views for a more granular search. Analyze these views like a detective, crafting your query for the targeted insights. sys.procedures and sys.sql_expression_dependencies are your new best friends:

SELECT DISTINCT p.name AS procedure_name
FROM sys.procedures p
JOIN sys.sql_expression_dependencies d ON p.object_id = d.referencing_id
WHERE d.referenced_entity_name = 'TableName'; -- Your table is under protection now


Here, we're catching the real perpetrators, the actual dependencies, not just textual suggestions. Replace 'TableName' to match your specific table name.

Dynamic dependencies using management views

Level-up your analysis by conscripting dynamic management views, like sys.dm_sql_referencing_entities, into your investigation:

SELECT 
    referencing_schema_name,
    referencing_entity_name,
    referencing_id, 
    referencing_class_desc, 
    is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TableName', 'OBJECT'); -- to boldly SQL where no one SQL-ed before


This point of view includes dynamically referenced objects, offering a more comprehensible perspective of the reality.

Activating SQL superpowers

Engage SQL Server with heightened awareness while dropping tables or making changes to your database. A detectives' best weapon? Their instinct. And yours is telling you to run these dependency checks before releasing anything into the wild:

-- avatar SQL, master of dependency checking, at your service
EXEC sp_MSdependencies N'TableName', null, 1315327;


The secret weapon: built-in stored procedures

If you're navigating the waters of an older SQL server version (2005 to be precise, sigh!), the sp_depends stored procedure is a gem:

EXEC sp_depends @objname = N'TableName'; -- Feelin' the 2005 vibes


Although deprecated in modern versions, this command gives you a quick peek into the world of object dependencies.