usp_tableReference

,

Recently I was faced with situation where I had to find all the store procedures referencing a table from within and across other databases. Since I did not get a code to get SPs from all the databases on the server, I came up with this script. Although, I am just searching for table name, it's possible there could be a table with same name but with different schema or in a different database.

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

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)