Tip for SQL Server: Find Text Used in SPs, Functions and Table

Introduction

This tip will help you to solve the issues when someone tells you about where this text is used in your SQL tables/SPs/Functions.

Background

A number of times, my PM told me to find stored procedures/functions where we have used specific text, then make changes to that part. So I have to check in individual SPs and functions for that text. 

Using the Code

This is simple code; you have to add this SP in your SQL server database.

CREATE PROCEDURE [dbo].[usp_Find]    
(    
 @vcrSearchString  VARCHAR(100)    
)    
AS    
BEGIN    
SET NOCOUNT ON    
    
  DECLARE @vcrSQL VARCHAR(1500)    
    
  SELECT @vcrSQL = 'SELECT  SO.name as Object,    
         COUNT(*) as Occurences, ' +    
         'CASE ' +    
           ' WHEN SO.xtype = ''D'' THEN ''Default'' ' +    
           ' WHEN SO.xtype = ''FN'' THEN ''Function'' ' +    
           ' WHEN SO.xtype = ''F'' THEN ''Foreign Key'' ' +    
           ' WHEN SO.xtype = ''P'' THEN ''Stored Procedure'' ' +    
           ' WHEN SO.xtype = ''PK'' THEN ''Primary Key'' ' +    
           ' WHEN SO.xtype = ''S'' THEN ''System Table'' ' +    
           ' WHEN SO.xtype = ''TR'' THEN ''Trigger'' ' +    
           ' WHEN SO.xtype = ''V'' THEN ''View'' ' +    
           'END AS TYPE ' +    
      'FROM   dbo.syscomments as SC    
      JOIN   dbo.sysobjects as SO ON SC.id = SO.id ' +    
      'WHERE   PATINDEX(''%' + @vcrSearchString + '%'', SC.text ) > 0 ' +     
      'GROUP BY  SO.name, SO.xtype ' +    
         'UNION ' +    
         'SELECT  SUBSTRING(SO.name, 1, 50 ) as Object,    
            1 as Occurances,    
            ''User Table'' as TYPE    
         FROM   SYSOBJECTS as SO    
         INNER JOIN  SYSCOLUMNS as SC on SC.id = SO.id    
         WHERE   SC.name LIKE ''%' + @vcrSearchString + _
         '%'' AND SO.xtype =' + '''U'''    
    
      
  EXECUTE( @vcrSQL )    
    
SET NOCOUNT OFF    
END

 How to use this Query in DB:

EXEC Usp_Find 'tblX' 
GO;
 EXEC Uso_Find 'Insert into tblY' 

Points of Interest

SQL Server makes it easy.