How to find all the stored procedures that reference a specific object ?

Usually, people tries to find all the stored procedures that reference a specific object. This object could be any table, view or even any text that is placed in the procedure, In all such cases this post will be really of much help to all of them.

SQL SERVER 2000

Let's say you are searching for 'objectName' in all your stored procedures then all you have to do is :

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE='PROCEDURE'

Another way to perform a search is through the system table syscomments:

SELECT OBJECT_NAME(id) FROM syscomments
WHERE [text] LIKE '%objectName%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

Now, why to use GROUP BY? Well, there is a curious distribution of the procedure text in system tables if the procedure is greater than 8KB. So, the above makes sure that any procedure name is only returned once, even if multiple rows in or syscomments draw a match. But, that begs the question, what happens when the text you are looking for crosses the boundary between rows? Here is a method to create a simple stored procedure that will do this, by placing the search term (in this case, 'objectName') at around character 7997 in the procedure. This will force the procedure to span more than one row in syscomments, and will break the word 'objectName' up across rows.

Run the following query in Query Analyzer, with results to text (CTRL+T):

SET NOCOUNT ON
SELECT 'SELECT '''+REPLICATE('x', 7936)+'objectName' SELECT REPLICATE('x', 500)+''''

This will yield two results. Copy them and inject them here:

CREATE PROCEDURE dbo.x
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Now, try and find this stored procedure in INFORMATION_SCHEMA.ROUTINES or syscomments using the same search filter as above. The former will be useless, since only the first 8000 characters are stored here. The latter will be a little more useful, but initially, will return 0 results because the word 'objectName' is broken up across rows, and does not appear in a way that LIKE can easily find it. So, we will have to take a slightly more aggressive approach to make sure we find this procedure. Your need to do this, by the way, will depend partly on your desire for thoroughness, but more so on the ratio of stored procedures you have that are greater than 8KB. In all the systems that I manage, I don't have more than a handful that approach this size, so this isn't something I reach for very often. Maybe for you it will be more useful. First off, to demonstrate a little better (e.g. by having more than one procedure that exceeds 8KB), let's create a second procedure just like above. Let's call it dbo.y, but this time remove the word 'objectName' from the middle of the SELECT line.

CREATE PROCEDURE dbo.y
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Basically, what we're going to do next is loop through a cursor, for all procedures that exceed 8KB. We can get this list as follows:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
HAVING COUNT(*) > 1

We'll need to create a work table to hold the results as we loop through the procedure, and we'll need to use UPDATETEXT to append each row with the new 8000-or-less chunk of the stored procedure code.

-- create temp table
CREATE TABLE #temp ( Proc_id INT, Proc_Name SYSNAME, Definition NTEXT )
-- get the names of the procedures that meet our criteria
INSERT #temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

-- initialize the NTEXT column so there is a pointer
UPDATE #temp SET Definition = ' '
-- declare local variables
DECLARE @txtPval binary(16), @txtPidx INT, @curName SYSNAME, @curtext NVARCHAR(4000)

-- set up a cursor, we need to be sure this is in the correct order
-- from syscomments (which orders the 8KB chunks by colid)

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_NAME(id), text
FROM syscomments s
INNER JOIN #temp t
ON s.id = t.Proc_id
ORDER BY id, colid OPEN c FETCH NEXT FROM c INTO @curName, @curtext

-- start the loop
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the pointer for the current procedure name / colid

SELECT @txtPval = TEXTPTR(Definition)
FROM #temp
WHERE Proc_Name = @curName

-- find out where to append the #temp table's value

SELECT @txtPidx = DATALENGTH(Definition)/2
FROM #temp
WHERE Proc_Name = @curName
-- apply the append of the current 8KB chunk
UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext
FETCH NEXT FROM c INTO @curName, @curtext
END
-- check what was produced
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2
FROM #temp
-- check our filter
SELECT Proc_Name, Definition
FROM #temp
WHERE definition LIKE '%objectName%'
-- clean up
DROP TABLE #temp
CLOSE c
DEALLOCATE c

SQL SERVER 2005
In SQL Server 2005 there are new functions like OBJECT_DEFINITION, which returns the whole text of the procedure. Also, there is a new catalog view, sys.sql_modules, which also holds the entire text, and INFORMATION_SCHEMA.ROUTINES has been updated so that the ROUTINE_DEFINITION column also contains the full text of the procedure. So, any of the following queries will work to perform this search in SQL Server 2005:

SELECT Name FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id)
LIKE '%objectName%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules
WHERE Definition LIKE '%objectName%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE = 'PROCEDURE'

Hats off for the beautifully explained and really useful article at Aspfaq.