[SQL] 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] is there a refactor
Hi John, everything you need is stored in these tables: http://www.postgresql.org/docs/7.4/static/catalog-pg-attribute.html http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html http://www.postgresql.org/docs/7.4/static/catalog-pg-namespace.html On Tue, Apr 5, 2011 at 4:27 PM, 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 > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] is there a refactor
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_test table2 table1 hours jedit_test topics t2 stone serial_test messages binary_test user_test timestamp_test role_t py_test money_test lock_test local_1 lang_test interval_test foob fooa fldlength fk_1 default_test csv_null check_two check_test array_test (29 rows) -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] is there a refactor
You could also do something like:
select nspname, relname, attname
from pg_attribute a
JOIN pg_class c ON (a.attrelid = c.oid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
where a.attname ~ 'yourcolumn'
and c.relname !~ 'pg'
and n.nspname not in ('pg_catalog','information_schema')
order by 1,2,3;
I'd functionalize it.
On Tue, Apr 5, 2011 at 10:44 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
>
> user_test
> table2
> table1
> hours
> jedit_test
> topics
> t2
> stone
> serial_test
> messages
> binary_test
> user_test
> timestamp_test
> role_t
> py_test
> money_test
> lock_test
> local_1
> lang_test
> interval_test
> foob
> fooa
> fldlength
> fk_1
> default_test
> csv_null
> check_two
> check_test
> array_test
> (29 rows)
>
> --
> Adrian Klaver
> [email protected]
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Peter Steinheuser
[email protected]
Re: [SQL] is there a refactor
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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
