String searching

String searching

Sometimes you need to search all procedures, or views for object names.... maybe you're going to rename something, or maybe you have to alter a parameter size/type, and you need to determine everywhere it may be.  This is a handy little tool I put together ages ago to do just that.  It resides in my dba working database, which resides on all of my servers, and I have coded it to 'USE @dbname', so you can hit any database on each instance.  Check it out, let me know what you think.

/****** Object:  StoredProcedure [dbo].[usp_StringSearch]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[usp_StringSearch] (
 @dbname VARCHAR(25),
 @string VARCHAR(35),
 @debug BIT = 0
)
AS
SET NOCOUNT ON;
/* Allows me to traverse system objects for any reference to the given @string.  
Helpful if/when renaming or decommissioning objects.
EXEC dbo.usp_StringSearch @dbname='testdatabase',@string='%TBL_%'
*/


DECLARE @sql1 NVARCHAR(2000)
SET @sql1 = 'USE '+@dbname+'
SELECT DISTINCT OBJECT_NAME(ID) AS CallingObject
FROM sys.syscomments
WHERE [Text] LIKE '''+@string+'''
ORDER BY OBJECT_NAME(ID) ' 


IF(@debug = 1)
BEGIN
   PRINT(@sql1)
END
ELSE
BEGIN
   EXEC (@sql1)
END


SET NOCOUNT OFF;
GO


NOTE:  Use @debug if you want to be sure of the string you're passing, like this:
EXEC dbo.usp_StringSearch @dbname='testdatabase',@string='%TBL_%',@debug = 1


Output:

   USE testdatabase
   SELECT DISTINCT OBJECT_NAME(ID) AS CallingObject
   FROM sys.syscomments
   WHERE [Text] LIKE '%TBL_%'
   ORDER BY OBJECT_NAME(ID)