I need to figure out if a particular SRID is used by any geometry in a database having plenty of different schemas, each of them having also plenty of tables, some having more than one geometry field.
How could I figure out the SRID of all the geometries (including the geometry field name, the table and the schema it belongs to) which are hold in a PostGIS database (pg:15 postgis:3.3)?
This is basically listing all columns having a USER-DEFINED
data types, which in a PostGIS enabled database correspond to the geometry columns, but in case one has other USER-DEFINED
data types, this won't perfectly work:
SELECT
table_schema,
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND data_type = 'USER-DEFINED'
ORDER BY table_schema, table_name;