Searching a SQL Server database's sys.comments table has long been a quick way to find stored procedure usage of another database, table, or column. Likewise, clicking "view dependencies" on a table is a quick way to find the opposite: which stored procedures (on the local db) refer to that table. I recently came across a scenario where neither met my needs, so I had to expand the capabilities of the former. The limitation of the first approach is parsing the text field to find multiple items. CHARINDEX and SUBSTRING functions only get you to the first, and looping can be incorrect as line breaks may occur haphazardly mid-text.

A client wants to decommission a legacy data warehouse so all stored procedures which extract data from it need to be remapped to a new data source. How do you retrieve all the tables that any stored procedure refers to and compile a report of table names and counts? The below solution is a quick method which isn't overly clever, but very useful. For this example, I ran the code on a database called "Extracts" which houses the stored procedures that pull data from the database "Datawarehouse". Temp tables rather than variable tables are necessary in this solution because of the usage of dynamic SQL.

First, compile a list of procedure names which refer to your database in question. We need to loop through the table of stored procedure names and use the sp_helptext system procedure to dump the contents of the procedure into the table, with the name of the stored procedure in the other column. This process is more accurate than directly using sys.comments because the line breaks are set by the developer and are compiled, thus complete. Note: feel free to use your looping mechanism of choice; I used a cursor but respect the ongoing "evil cursors" debate. The @ToFind and @Limiter variables are declared and set at the top of the SQL to promote reusability.

DECLARE @ToFind VARCHAR(200), 
 @Limiter VARCHAR(200), 
 @Name VARCHAR(200), 
 @SQL VARCHAR(3000) 
 
SELECT @ToFind = 'Datawarehouse.dbo.', 
 @Limiter = ' ' 
 
CREATE TABLE #text 
 ( 
 name VARCHAR(200), 
 text VARCHAR(MAX) 
 ) 
 
DECLARE _cursor_ CURSOR FOR 
 SELECT DISTINCT a.name 
 FROM sys.objects a 
 INNER JOIN syscomments b 
 ON a.object_id = b.id 
 AND b.text LIKE '%' + @ToFind + '%' 
 
OPEN _cursor_ 
 
FETCH NEXT FROM _cursor_ INTO @Name 
 
WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SELECT @SQL = 'INSERT INTO #TEXT(Text) EXEC sp_helpText ' 
 + @Name 
 
 EXEC (@SQL) 
 
 UPDATE #text 
 SET name = @Name 
 WHERE name IS NULL 
 
 FETCH NEXT FROM _cursor_ INTO @Name 
 END 
 
CLOSE _cursor_ 
 
DEALLOCATE _cursor_ 

This process took 00:11 seconds and the #Text table holds 47,903 rows. Much of this data is useless to us as all content from the stored procedures is present. We are only interested in obtaining the references to tables keyed with "Datawarehouse.dbo.". I chose to save the name of the stored procedure with the text. Even though my final output does not make use of this information, it's likely the client may wish to have the breakdown by stored procedure, which is easily producible with this field.

At first I thought I could just query this list for instances of "Datawarehouse.dbo." but I realized it's possible that developers put multiple references to tables on the same line; a looping practice is still needed. Again, to promote reusability, I created a string search function which returns a list of occurrences of a "Text to Find" up until a limiter.

CREATE FUNCTION [dbo].[Ufn_stringsearch](@Text VARCHAR(MAX), 
 @ToFind VARCHAR(200), 
 @Limiter VARCHAR(200)) 
RETURNS @Output TABLE( 
 foundtext VARCHAR(MAX)) 
AS 
 BEGIN 
 --Index is always the starting position of the ToFind text 
 DECLARE @INDEX INT, 
 @LENGTH INT 
 
 SELECT @INDEX = Charindex(@ToFind, @Text), 
 @LENGTH = Len(@Text) 
 
 --Insure that there is text we care about and still a limiter present 
 WHILE @INDEX > 0 
 AND Charindex(@Limiter, @Text) > 0 
 BEGIN 
 --Trim the Text to begin with what we care about (ToFind) and reset Length 
 SELECT @Text = SUBSTRING(@Text, @INDEX, @LENGTH) 
 
 SELECT @LENGTH = Len(@Text) 
 
 --Save the ToFind up until the Limiter (giving everything inbetween) --If there's no limiter present, just insert the ext as is. 
 INSERT INTO @Output 
 SELECT CASE 
 WHEN Charindex(@Limiter, @Text) = 0 THEN @Text 
 ELSE SUBSTRING(@Text, 0, Charindex(@Limiter, @Text)) 
 END 
 
 --Now find the start of ToFind text in the rest of the Text, after the Limiter 
 SELECT @Text = SUBSTRING(@Text, Charindex(@Limiter, @Text) + 1, 
 @LENGTH) 
 
 SELECT @INDEX = Charindex(@ToFind, @Text) 
 END 
 
 RETURN 
 END 

By returning a table, the function is easily joined to with the CROSS APPLY statement. Thus the results of our inquiry, "how many times these Datawarehouse tables appear in the stored procedures", is produced with:

SELECT Rtrim(REPLACE(b.foundtext, @ToFind, '')), 
 COUNT(*) 
FROM #text a 
 CROSS apply dbo.Ufn_stringsearch(a.text, @ToFind, @Limiter) b 
GROUP BY Rtrim(REPLACE(b.foundtext, @ToFind, '')) 
ORDER BY 1 

Here, I simply stripped out the "Datawarehouse.dbo." prefix of the outputted data. This query returns my data (152 tables and a count for each table) in 00:08 seconds. This process is also useful for finding column references, developer comments, or any other fixed text assuming you have a beginning and ending criteria.