usp_tableReference

CREATE PROCEDURE usp_tableReference ( @tableName   varchar(256))
/*
Author:Ankush Parab
Created on:2013/08/01
Dependency:DMV- sys.sql_modules
Purpose:To find the SPs which are referencing the table passed as argument from all databases on server.
*/AS
BEGIN
CREATE TABLE  #retSPs
(
dbName varchar(256),
spName varchar(1024)
)
 
declare @cmd varchar(1024)
set @cmd = 'use ?;INSERT INTO #retSPs(dbName, spName) 
SELECT ''?'',
OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition like ''%'+ 
@tableName + '%'''

exec sp_MSforeachdb @cmd

SELECT *
FROM #retSPs
DROP TABLE #retSPs

END;
GO