Finding a string occurance in Tables/View/SP...

CREATE proc sp_dba_find_string (@Search_Stringvarchar(100))
as
/* 
   AUTHOR
Burhanettin MASAK, burhanettin@yahoo.com 
   INFO
Lists the objects where string occurs
   ASSUMPTIONS
      - None
   PARATEMETERS
      @Search_String = String we're looking for 
   EXAMPLES
        sp_dba_find_string
   RELATED SP
      - None
*/
--declare@Search_Stringvarchar(100)

create table #TEMP_SEARCHSTRING (line varchar(200))

set nocount on

select @Search_String = '%' + @Search_String + '%'

-- TABLES

-- Table Names
insert 
#TEMP_SEARCHSTRING 
select 
'Table               : ' + left(name, 40)  
from 
sysobjects (nolock) 
where 
name like @Search_String
and type = 'U'
 
-- Field Names
insert 
#TEMP_SEARCHSTRING 
select 
'Table Field         : ' + left(a.name, 40) + left(b.name,40) 
from 
sysobjects a (nolock), syscolumns b (nolock) 
where 
a.id = b.id 
anda.type = 'U'
andb.name like @Search_String
order by 
a.name

-- VIEWS

-- View Names
insert 
#TEMP_SEARCHSTRING 
select 
'View                : ' + left(name, 40) 
from 
sysobjects (nolock) 
where 
name like @Search_String
and type = 'V'
 

-- Field Names
insert 
#TEMP_SEARCHSTRING 
select 
'View Fields         : ' + left(a.name, 40) + left(b.name,40) 
from 
sysobjects a (nolock), syscolumns b (nolock) 
where 
a.id = b.id 
anda.type = 'V'
andb.name like @Search_String
order by 
a.name
 

-- PROCEDURES
insert 
#TEMP_SEARCHSTRING 
select 
'Procedure           : ' + left(name,40) 
from 
sysobjects (nolock) 
where 
name like @Search_String
and type = 'P'
 
insert 
#TEMP_SEARCHSTRING 
select 
'Procedure Text      : ' + left(a.name,40) 
from 
sysobjects a (nolock), syscomments b (nolock) 
where 
a.id = b.id
anda.type = 'P'
andb.text like @Search_String
order by 
name
 

-- TRIGGERS
insert 
#TEMP_SEARCHSTRING 
select 
'Trigger             : ' + left(name,40) 
from 
sysobjects (nolock) 
where 
name like @Search_String
and type = 'TR'
 

-- Field Names
insert 
#TEMP_SEARCHSTRING 
select 
'Trigger Field       : ' + left(a.name,40) 
from 
sysobjects a (nolock), syscomments b (nolock) 
where 
a.id = b.id
anda.type = 'TR'
andb.text like @Search_String
order by 
name
 

-- INDEXES
insert 
#TEMP_SEARCHSTRING 
select 
'Index               : ' + left(name, 40) 
from 
sysindexes (nolock) 
where 
name like @Search_String
 

-- SYSTEM TABLES

-- Table Names
insert 
#TEMP_SEARCHSTRING 
select 
'System Table        : ' + left(name, 40) 
from 
sysobjects (nolock) 
where 
name like @Search_String
and type = 'S'
 
-- Field Names
insert 
#TEMP_SEARCHSTRING 
select 
'System Table Field  : ' + left(a.name, 40) + left(b.name,40) 
from 
sysobjects a (nolock), syscolumns b (nolock) 
where 
a.id = b.id 
anda.type = 'S'
andb.name like @Search_String
order by 
a.name

select 
* 
from 
#TEMP_SEARCHSTRING 

drop table #TEMP_SEARCHSTRING 


GO