Technical Article

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_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

Rate

Share

Share

Rate