Home > SQL > Find Table Name in Store Procedure

Find Table Name in Store Procedure

To find all stored procedure where particular table is used

SELECT DISTINCT OBJ.name, OBJ.xtype
FROM syscomments CMT
INNER JOIN sysobjects OBJ ON CMT.id=OBJ.id
WHERE CMT.TEXT LIKE ‘%TableName%’

And I was just surfing the net and got this. It really made my life easy though I have to add some code to match my use case.

;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = ‘P’)

SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: