Monthly Archives: September 2012

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

%d bloggers like this: