Search For Dependencies

A stored procedure that finds which SQL modules reference a given string in their definitions.

Published Jan 20, 2006

Before you drop a column or rename an object, find out what depends on it. This tiny stored procedure searches the definition text of every SQL module in the database (via syscomments) for a given string and lists each dependent object. Crude but effective for quick dependency checks.

Example: EXEC SearchForDepenencies @Text = 'Customers'

CREATE PROCEDURE SearchForDepenencies
(
	@Text Varchar(255)
) AS
BEGIN
	SELECT
		id AS [Object],
		object_name(id) AS [Dependent]
	FROM
		syscomments
	WHERE
		Convert(VarChar(max), text) LIKE '%' + @Text + '%'
END