[postgis-users] Retrieve all tables and views with geometry columns

[Michael Fuhr]

On Wed, Aug 01, 2007 at 11:42:15AM +0200, Boehm, Andreas wrote:
> I would like to list all the geometry columns in a database. So the user
> can select the features he or she wants to see. Therefore I need to
> retrieve the database's metadata. 
> With "select * from geometry_columns" I'm able to get a list of all
> tables with geometry columns. But I don't get information about the
> _views_ with a geometry column. 
> Do I have to parse the definition text in pg_views? Maybe there is an
> easier way...

You could query pg_catalog.pg_attribute or information_schema.columns.

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html

SELECT n.nspname, c.relname, a.attname
  FROM pg_attribute AS a
  JOIN pg_class AS c ON c.oid = a.attrelid
  JOIN pg_namespace AS n ON n.oid = c.relnamespace
 WHERE a.atttypid = 'geometry'::regtype
   AND NOT a.attisdropped
   AND c.relkind IN ('r', 'v')
 ORDER BY n.nspname, c.relname, a.attname;

or

SELECT table_schema, table_name, column_name
  FROM information_schema.columns
 WHERE udt_name = 'geometry'
 ORDER BY table_schema, table_name;

-- 
Michael Fuhr