May 30, 2009 at 12:46 am
Hi All
example If I retrieve the item_code = 'Pr1001'
This Code may be in 4 o 5 table how can we search that Entire database..
My Query is for a Table
Select * from T_name where item_code like '%PR1001%'
But I need to search the entire database with sible query.. which table are having this item-code?
Very Urgent..
thanks in advance..
May 30, 2009 at 1:05 am
Yes my friend this is possible :
let me give you an example :
create table mytestdb
go
create table test (t1 int)
create table test1 (t1 int)
create table test2 (t1 int)
then i inserted some values :
select * from test
t1
-----------
1
1
1
2
2
select * from test1
t1
-----------
2
2
3
3
3
select * from test2
t1
-----------
3
3
3
1
1
1
now , run this
sp_msforeachtable "Select * from ? where t1 like '%1%'"
t1
-----------
1
1
1
t1
-----------
t1
-----------
1
1
1
So this is only possible if the column name is same in all the tables ..
Else not ...but believe me even this is superb ...what else can you expect 🙂
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 30, 2009 at 1:06 am
Saravanan_tvr (5/30/2009)
But I need to search the entire database with sible query.. which table are having this item-code?
Hi,
Its Possible!
Your Item_Code column name is same in the other tables with in the DB?
ARUN SAS
May 30, 2009 at 1:24 am
It Great Reply
Hearty Thanks to hi_abhay78!
May 30, 2009 at 2:20 am
See the following article that exactly does the same.
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--Ramesh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply