SQL Code: Find TABLES IN A STORE Procedure

Find TABLES IN A STORE Procedure

USE database

---------------------------------------------------------------- DECALRE TABLE NAME -------------------------------------------------

DECLARE @TableName VARCHAR(100) = 'HolidayList'

-------------------------------------------------------------------------------------------------------------------------------------

SELECT Name [Store Procedure Found In]

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+ @TableName +'%'

-------------------------------------------------------------------------------------------------------------------------------------

SELECT o.Name [Table Name]

, sp.Name [Found In]

, sp.type_desc [Type]

FROM sys.objects o

JOIN sys.sql_expression_dependencies sd

ON o.object_id = sd.referenced_id

JOIN sys.objects sp

ON sd.referencing_id = sp.object_id

AND sp.type in ('P', 'FN')

WHERE o.name LIKE '%'+ @TableName +'%'

GROUP BY o.Name, sp.Name, sp.type_desc

ORDER BY sp.Name

-------------------------------------------------------------------------------------------------------------------------------------

SELECT o.name [Found In]

, o.type [Type]

, o.type_desc [Type Description]

FROM sys.sql_modules sm

JOIN sys.objects o

ON o.object_id = sm.object_id

WHERE sm.definition LIKE '%'+ @TableName +'%'

ORDER BY o.type, o.name

-------------------------------------------------------------------------------------------------------------------------------------

SELECT so.name [Found In]

, text [Code]

FROM sysobjects so

JOIN syscomments sc

ON so.id = sc.id

WHERE UPPER(text) LIKE '%'+ @TableName +'%'

SQL Code