Simple Object and String Search within Objects
This Simple script will help you to do a Server wide search for an object name or usage of a String in Object Code like Function, Procedure, Views
Just Type in the Text to Search or Object Name to find against the Parameter @SearchText and Execute.
The output will be in a Tabular format with the Server Name, Database Name, Object Name, Object Type and It First Occurance ( within the procedure/ View/ Function Codes)
Make Sure you have access to the Object Code before Executing the script to search
/***************************************************************************
Type: Utility Script
Scripted by : Kartik M
Purpose: Searching Text Within Object code, in all database of a SQL Server
Limitation: Execution user must have permission to view code of the object
Usage: Enter Search Text against the Parameter @SearchText
Disclaimer : This script is published with NO WARRANTY.Please test thoroughly in your test environment before use it as a production utility
*****************************************************************************/Use master
Go
Declare @Command nvarchar(2000)
Declare @SearchText varchar(200) = 'SearchText' -- Enter Search Text here
if Exists (Select 1 from tempdb..sysobjects where xtype = 'U' and Name = '##T001')
Drop Table ##t001
Create Table ##t001
(DBName varchar(100), ObjectName Varchar(200), ObjectType Varchar(100), [Text] Varchar(8000))
Set @command = N'Insert into ##t001 Select ''[?]'' DataBaseName, o.name Object_name,o.type_desc ObjectType, Replace(c.text, Char(13)+char(10), '' '')Text from [?]..syscomments c Inner join [?].sys.objects o on o.object_id = c.id where REPLACE(REPLACE(c.text,''['',''''),'']'','''') like ''%'+@SearchText+'%'''
Execute sp_MSforeachdb @command, N'?'
Set @Command = ''
Set @command = N'Insert into ##t001 Select ''[?]'' DataBaseName, o.name Object_name,o.type_desc ObjectType, '''' from [?].sys.objects o Where o.name like ''%'+@SearchText+'%'''
Execute sp_MSforeachdb @command, N'?'
Select @SearchText SearchText, @@ServerName Server,DBName, ObjectName,ObjectType, SUBSTRING([Text],CHARINDEX(@SearchText,[Text],1)-1,CHARINDEX(' ',[Text],CHARINDEX(@SearchText,[Text],1)))FirstOccurance from ##t001
Order by ObjectType, DBName, ObjectName 
