CODEWITHSUNDEEP
sqlsql-servert-sql
Ankita
Ankita

Reputation: 57

how to find if a table is being read or written in a procedure?

I have a table named 'abc_tbl' and i want to know if it is being read (SELECT) or written (INSERT, UPDATE, DELETE) in a list of 100 procedures which are using this table.

Upvotes: 4

Views: 232

Answers (2)

fez
fez

Reputation: 1835

You can use information_schema.routines to find procedures referencing your table abc_tbl.

The SQL_DATA_ACCESS column will return one of the following values:

  1. NONE
  2. CONTAINS
  3. READS
  4. MODIFIES

Link to MSDN documentation

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

Upvotes: 1

Bhanu Chandra
Bhanu Chandra

Reputation: 408

Try This 

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

Upvotes: 0

Related Questions