Stored Procedure Schema

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.

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 

How to use this procedure.

Here the parameter one 
is the Keyword for scanning. 
Second Parameter used for Searching on 
the specific object FN = Scalar function, IF = In-lined table-function,P
 = Stored procedure,TR = Trigger,V = View
Third paramter is the 
database name, this is the optional parameter, if not given any database
 name it will scan all the database. 

FindProc "Crack","P"