Thread: is there a refactor
Hi, I would like to have a simple way to retrieve information for a field name. By that I mean have some SQL select that will return all the tables a field name exist within a database. I did not find anything with google but of course google depends on the search string. Thanks in advance, Johnf
Hi John,<br /><br />everything you need is stored in these tables:<br /><a href="http://www.postgresql.org/docs/7.4/static/catalog-pg-attribute.html">http://www.postgresql.org/docs/7.4/static/catalog-pg-attribute.html</a><br /><a href="http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html">http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html</a><br /><a href="http://www.postgresql.org/docs/7.4/static/catalog-pg-namespace.html">http://www.postgresql.org/docs/7.4/static/catalog-pg-namespace.html</a><br /><br/><br /><div class="gmail_quote">On Tue, Apr 5, 2011 at 4:27 PM, John Fabiani <span dir="ltr"><<a href="mailto:johnf@jfcomputer.com">johnf@jfcomputer.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hi,<br /> I would like tohave a simple way to retrieve information for a field name.<br /> By that I mean have some SQL select that will returnall the tables a field<br /> name exist within a database. I did not find anything with google but of<br /> coursegoogle depends on the search string.<br /><br /> Thanks in advance,<br /> Johnf<br /><font color="#888888"><br /> --<br/> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> Tomake changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all" /><br/>-- <br />---------------------------------------<br />Viktor Bojović<br />---------------------------------------<br/>Wherever I go, Murphy goes with me<br />
On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote: > Hi, > I would like to have a simple way to retrieve information for a field name. > By that I mean have some SQL select that will return all the tables a field > name exist within a database. I did not find anything with google but of > course google depends on the search string. > > Thanks in advance, > Johnf test(5432)aklaver=>SELECT table_name from information_schema.columns where column_name = 'id'; table_name ----------------user_testtable2table1hoursjedit_testtopicst2stoneserial_testmessagesbinary_testuser_testtimestamp_testrole_tpy_testmoney_testlock_testlocal_1lang_testinterval_testfoobfooafldlengthfk_1default_testcsv_nullcheck_twocheck_testarray_test (29 rows) -- Adrian Klaver adrian.klaver@gmail.com
You could also do something like:<br /><br />select nspname, relname, attname <br />from pg_attribute a<br /> JOIN
pg_classc ON (a.attrelid = c.oid)<br /> JOIN pg_namespace n ON (n.oid = c.relnamespace)<br />where a.attname ~
'yourcolumn'<br/> and c.relname !~ 'pg'<br />and n.nspname not in ('pg_catalog','information_schema')<br />order by
1,2,3;<br/><br />I'd functionalize it.<br /><br /><div class="gmail_quote">On Tue, Apr 5, 2011 at 10:44 AM, Adrian
Klaver<span dir="ltr"><<a href="mailto:adrian.klaver@gmail.com">adrian.klaver@gmail.com</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;">On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote:<br /> > Hi,<br /> > I would like to
havea simple way to retrieve information for a field name.<br /> > By that I mean have some SQL select that will
returnall the tables a field<br /> > name exist within a database. I did not find anything with google but of<br />
>course google depends on the search string.<br /> ><br /> > Thanks in advance,<br /> > Johnf<br /><br />
test(5432)aklaver=>SELECTtable_name from information_schema.columns where<br /> column_name = 'id';<br />
table_name<br/> ----------------<br /> user_test<br /> table2<br /> table1<br /> hours<br /> jedit_test<br />
topics<br/> t2<br /> stone<br /> serial_test<br /> messages<br /> binary_test<br /> user_test<br />
timestamp_test<br/> role_t<br /> py_test<br /> money_test<br /> lock_test<br /> local_1<br /> lang_test<br />
interval_test<br/> foob<br /> fooa<br /> fldlength<br /> fk_1<br /> default_test<br /> csv_null<br />
check_two<br/> check_test<br /> array_test<br /> (29 rows)<br /><br /> --<br /> Adrian Klaver<br /><a
href="mailto:adrian.klaver@gmail.com">adrian.klaver@gmail.com</a><br/><font color="#888888"><br /> --<br /> Sent via
pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all"
/><br/>-- <br />Peter Steinheuser<br /><a href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br
/>
On Tuesday, April 05, 2011 07:44:51 am Adrian Klaver wrote: > On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote: > > Hi, > > I would like to have a simple way to retrieve information for a field > > name. By that I mean have some SQL select that will return all the > > tables a field name exist within a database. I did not find anything > > with google but of course google depends on the search string. > > > > Thanks in advance, > > Johnf > > test(5432)aklaver=>SELECT table_name from information_schema.columns where > column_name = 'id'; > table_name Wow that was quick - thanks to all! Johnf