getting all columns that match a name

[George Parkinson]

hi bart,

i was afraid it was going to be something like that (procedure + loop)

thanks for your help
george



________________________________
 From: Bart Pietercil <bart.pietercil at cognosis.be>
To: Valentina Developers <valentina at lists.macserve.net> 
Sent: Wednesday, May 22, 2013 11:24:44 AM
Subject: Re: getting all columns that match a name
 


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
>

_______________________________________________
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/50a94109/attachment.html>