AW: [postgis-users] Retrieve all tables and views with geometrycolumns

[Thorsten Kraus]

Hello,

I would like to drop all tables from my database which contain geometry columns. Does someone have a solution for this?

Regards,
Thorsten

-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]Im Auftrag von
Michael Fuhr
Gesendet: Mittwoch, 1. August 2007 13:00
An: PostGIS Users Discussion
Betreff: Re: [postgis-users] Retrieve all tables and views with
geometrycolumns


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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users