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