On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:
> For 7.3, the info you need is available in the system catalogs, which
> have a somewhat hairier layout than the SQL-standard information_schema.
Using Doug's pointer, I came up with this for 7.3...
SELECT
a.relname,b.attname,c.typname,b.attlen,b.atttypmod
FROM
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
WHERE
c.typname ILIKE '%XX%'
AND b.attname ILIKE '%YY%'
AND a.relname ILIKE '%ZZ%'
AND b.attisdropped=false
ORDER BY
a.relname,b.attname
...you replace XX, YY and ZZ with a substring you want to find in the
field, and/or table, and/or type. The select will return all matching
fields in a reasonble fashion.
What I've not figured out yet is how this relates to a particular
database; if a table and field match in two databases, you'll see them
both, which (probably) isn't what you'd want.
Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.
--Ben
