getting all columns that match a name

[Bart Pietercil]

stored procedure


PSEUDO CODE won't work but I have to go now; hope you get the idea

declare looper,i ,y integer;
mylist string;

set i = select count() from (show tables);
declare mycursor = show tables
open cursor mycursor
set looper = 0
while looper < i do
	set looper = looper + 1
	set aTable = tablename
	set y = select count(id) from (show fields of aTable) where fld_name = 'productID'; 
	if y > 0 then
	add atable to mylist
	end if
	if looper < i then
	go next
	end if
end  while
close mycursor
select mylist;


hth

bart

On 22 May 2013, at 17:08, George Parkinson <george at microtherapy.ca> wrote:

> yes indeed, but i'm looking for all table names that have a field 'productid' (in one query)
> 
> thanks
> george
> 
> From: Bart Pietercil <bart.pietercil at cognosis.be>
> To: Valentina Developers <valentina at lists.macserve.net> 
> Sent: Wednesday, May 22, 2013 10:59:12 AM
> Subject: Re: getting all columns that match a name
> 
> select * from (show fields of tblName) where fld_name = 'productID';
> 
> mind you check fieldname to use for fld_name by doing a 'show fields of tblName' first
> 
> hth,
> 
> Bart 
> 
> On 22 May 2013, at 16:54, George Parkinson <george at microtherapy.ca> wrote:
> 
>> hello all,
>> 
>> i'm trying to get a list of all table names in a db that have a specific column name.
>> ie all user tables with a column named 'productID'.
>> 
>> do i have to use a combination of SHOW TABLES, SHOW COLUMNS, and a loop or is there simpler way?
>> 
>> thanks,
>> george
>> 
>> 
>> 
>> 
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20130522/d184bc22/attachment-0001.html>