<html><head><meta http-equiv="Content-Type" content="text/html charset=iso-8859-1"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">stored procedure<div><div><br></div><div><br></div><div>PSEUDO CODE won't work but I have to go now; hope you get the idea</div><div><br></div><div>declare looper,i ,y integer;</div><div>mylist string;</div><div><br></div><div>set i = select count() from (show tables);</div><div>declare mycursor = show tables</div><div>open cursor mycursor</div><div>set looper = 0</div><div>while looper < i do</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>set looper = looper + 1</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>set aTable = tablename</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>set y = <span style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt; background-color: rgb(255, 255, 255); ">select count(id) from (show fields of aTable) where fld_name = 'productID';</span> </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>if y > 0 then</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>add atable to mylist</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>end if</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>if looper < i then</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>go next</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>end if</div><div>end while</div><div>close mycursor</div><div>select mylist;</div><div><br></div><div><br></div><div>hth</div><div><br></div><div>bart</div><div><br><div><div>On 22 May 2013, at 17:08, George Parkinson <<a href="mailto:george@microtherapy.ca">george@microtherapy.ca</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div><div style="background-color: rgb(255, 255, 255); font-family: 'lucida console', sans-serif; font-size: 10pt; position: static; z-index: auto; "><div><span>yes indeed, but i'm looking for all table names that have a field 'productid' (in one query)</span></div><div style="font-size: 13.600000381469727px; font-family: 'lucida console', sans-serif; background-color: transparent; font-style: normal; "><span><br></span></div><div>thanks</div><div>george</div><div><br></div> <div style="font-family: 'lucida console', sans-serif; font-size: 10pt;"> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"> <div dir="ltr"> <hr size="1"> <font size="2" face="Arial"> <b><span style="font-weight:bold;">From:</span></b> Bart Pietercil <<a href="mailto:bart.pietercil@cognosis.be">bart.pietercil@cognosis.be</a>><br> <b><span style="font-weight: bold;">To:</span></b> Valentina Developers <<a href="mailto:valentina@lists.macserve.net">valentina@lists.macserve.net</a>> <br> <b><span style="font-weight: bold;">Sent:</span></b>
Wednesday, May 22, 2013 10:59:12 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: getting all columns that match a name<br> </font> </div> <div class="y_msg_container"><br><div id="yiv514199923">select * from (show fields of tblName) where fld_name = 'productID';<div><br></div><div>mind you check fieldname to use for fld_name by doing a 'show fields of tblName' first</div><div><br></div><div>hth,</div><div><br></div><div>Bart </div><div><br><div><div>On 22 May 2013, at 16:54, George Parkinson <<a rel="nofollow" ymailto="mailto:george@microtherapy.ca" target="_blank" href="mailto:george@microtherapy.ca">george@microtherapy.ca</a>> wrote:</div><br class="yiv514199923Apple-interchange-newline"><blockquote type="cite"><div><div style="background-color: rgb(255, 255, 255); font-family: 'lucida console', sans-serif; font-size: 10pt;"><div id="yiv514199923"><div style="background-color: rgb(255, 255, 255); font-family: 'lucida
console', sans-serif; font-size: 10pt;"><div id="yiv514199923yui_3_7_2_31_1368763198479_39">hello all,</div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><br></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39">i'm trying to get a list of all table names in a db that have a specific column name.</div><div id="yiv514199923yui_3_7_2_31_1368763198479_39">ie all user tables with a column named 'productID'.</div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><br></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39">do i have to use a combination of SHOW TABLES, SHOW COLUMNS, and a loop <span style="font-size:10pt;">or is there simpler way?</span></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><span style="font-size:10pt;"><br></span></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><span style="font-size:10pt;">thanks,</span></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><span style="font-size:10pt;">george</span></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><span style="font-size:10pt;"><br></span></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><br id="yiv514199923yui_3_7_2_31_1368763198479_69"></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39" style="font-size: 13.600000381469727px; font-family: 'lucida console', sans-serif; background-color: transparent; font-style: normal;"><br></div><div id="yiv514199923yui_3_7_2_31_1368763198479_39"><br></div></div></div></div></div>_______________________________________________<br>Valentina mailing list<br><a rel="nofollow" ymailto="mailto:Valentina@lists.macserve.net" target="_blank" href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br><a href="http://lists.macserve.net/mailman/listinfo/valentina">http://lists.macserve.net/mailman/listinfo/valentina</a><br></blockquote></div><br></div></div><br>_______________________________________________<br>Valentina mailing list<br><a ymailto="mailto:Valentina@lists.macserve.net" href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br><a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br><br><br></div> </div> </div> </div></div>_______________________________________________<br>Valentina mailing list<br><a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>http://lists.macserve.net/mailman/listinfo/valentina<br></blockquote></div><br></div></div></body></html>