How to find, read or write a table in a procedure?

Question

I have a table called "abc_tbl" and I want to know if it is read (SELECT) or written (INSERT, UPDATE, DELETE) in a list of 100 procedures that use this table.

Answer 1

You can use information_schema.routinesto search for procedures that reference your table abc_tbl.

The column SQL_DATA_ACCESSreturns one of the following values:

• NONE
• CONTAINS
• IS READING
• modifies

MSDN Documentation Link

SELECT *
FROM information_schema.routines ISR
WHERE CHARINDEX('<your_schema_name>.abc_tbl', ISR.ROUTINE_DEFINITION) > 0
GO

Answer 2

try it

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN  'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%table_name%'
ORDER BY Location, ObjectName