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

,

This script search the active database for the string
you're looking for.
It searhes with in Table Names, Table Columns, Views, SP's, Triggers, Indexes and System Tables.

Enjoy it ...

CREATE proc sp_dba_find_string (@Search_String	varchar(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_String	varchar(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 
	and	a.type = 'U'
	and	b.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 
	and	a.type = 'V'
	and	b.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
	and	a.type = 'P'
	and	b.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
	and	a.type = 'TR'
	and	b.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 
	and	a.type = 'S'
	and	b.name like @Search_String
order by 
	a.name

select 
	* 
from 
	#TEMP_SEARCHSTRING 

drop table #TEMP_SEARCHSTRING 


GO

Rate

Share

Share

Rate