Find SQL Objects based on the Keywords..

Below code snippets contains the code to find the stored procedure based on the key word. Many times with the tough deliveries and short dead lines it can be possible that, we tend to forget the name of the stored procedure etc... So in that kind of situation this stored procedure will help you where in you can just type in the key word and it will look for that stored procedure. The best part of this procedure is scans all the databases attached on the server and returns you the result set.

Stored Procedure Schema

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF'))
	drop function [dbo].[Split]
GO

CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FindProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindProc]
GO


CREATE Proc FindProc    
(    
	 @pSearchText VarChar(800), -- Variable used for finding the table name    
	 @pObjectType VarChar(100) = '',-- Variable used for Searching on the specific object FN = Scalar function, IF = In-lined table-function,P = Stored procedure,TR = Trigger,V = View , '' = All the objects  
	 @DbName      Varchar(100) = ''-- Variable for searching into the specific database or all the Database if '' then search in all the database.   
)    
As    
Begin     
	Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query    
	  
	--Now The validations Checks for the Database  
	If Not Exists  
	  (  
		 Select 1 From master..Sysdatabases Where [Name] = @DbName Or @DbName = ''   
	  )    
	Begin   
	  Print 'The Database is not Accessable. Please Check the Options.'  
	  Return -- Bye Bye wrong Database :-)  
	End   
	  
	--Now The Validations Check for the ObjectID   
	IF Exists   
	  (  
		Select Data  From Dbo.Split(@pObjectType,',') C1
	    Where Not Exists    
				   (  
					  Select * From Dbo.Split('FN,V,IF,P,'''',TR,TF',',') C2 Where C1.Data = C2.Data
				   )  
	  )  
	Begin   
	  Print 'The Object Type Specification is In-Corrct. Please Check the Options.'  
	  Return   
	End   
	--Now Filtering on the basis of the ObjectID
	Declare @ObjectType1 varchar(100)
	Set @ObjectType1 = ''

	Select @ObjectType1 = @ObjectType1 +  '''' + Data + ''','
	From Dbo.Split(@pObjectType,',')
	
	Select @ObjectType1 = Case When 
								@ObjectType1 <> '' Then Left(@ObjectType1,Datalength(@ObjectType1)-1) 
							   Else 
									Null 
								End 

	Select @pObjectType = Case When 
								@ObjectType1 Is Null OR @ObjectType1 <> '' Then '''FN'',''V'',''IF'',''P'',''V'',''TF'',''TR''' 
							   Else 
									@ObjectType1 
							   End 

	create table #TblDataBase    
	(  
		 DbName varchar(1000),    
		 ProcName Varchar(80),  
		 ProcType Varchar(100)   
	)  
	Print 1 
	--  If the Specified Database is passed then.. 
	If @DbName <> ''  
	Begin 
		select @QryString = 'Select '''+ @DbName + ''' As DataBaseName, [Name],[Xtype] From 
			 [' + @DbName+']..Sysobjects sy Inner Join [' + @DbName + ']..SysComments Com On sy.[ID] = Com.[ID] 
			 Where Com.[Text] Like ''%' + @pSearchText + '%'' And XType In ( ' + @pObjectType + ')'
			 Print @QryString
		 Insert #TblDataBase exec (@QryString)  
	End 
	Else -- Looping through all the Databases..  
	Begin   
		select @DbName = ''    
		while @DbName < (select max(name) from master..sysdatabases )    
		begin    
			 select @DbName = min(name) from master..sysdatabases where [Name] > @DbName    
			 select @QryString = 'Select '''+ @DbName + ''' As DataBaseName, [Name],[Xtype] From [' + @DbName+']..Sysobjects sy Inner Join [' +   
   			 @DbName + ']..SysComments Com On sy.[ID] = Com.[ID] Where Com.[Text] Like ''%' + @pSearchText + '%'' And XType In (' + @pObjectType + ')'  
			 Print @QryString
			 Insert #TblDataBase exec (@QryString)    
		End   
	End  

	--Now Finally Select the Records and show to the user 
	Select Dbname As 'DataBase Name ', [ProcName] As 'Procedure Name',
	Case ProcType 
		 When 'V'  Then 'View'
		 When 'FN' Then 'Scalar function' 
		 When 'IF' Then 'In-lined table-function'
		 When 'P'  Then 'Stored procedure'
		 When 'TF' Then 'Table function'
		 When 'TR' Then 'Trigger'
	End As 'Type'
	From #TblDataBase    
	Order by 3
	    
	Drop Table #TblDataBase    
End 

FindProc "Crack","P"