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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
