CODEWITHSUNDEEP
sqlpostgresql
Anton Kim
Anton Kim

Reputation: 943

Return all tables where specific enum is used

I am trying to return all tables where a specific enum is used. So far I have this:

select *
    from pg_type t 
       join pg_enum e on t.oid = e.enumtypid  
       join pg_catalog.pg_namespace n
          ON n.oid = t.typnamespace and t.typname = 'enum';

How could I modify this query to return the actual tables, or is there something altogether different that is better to use?

Upvotes: 0

Views: 98

Answers (2)

DSKalugin
DSKalugin

Reputation: 56

select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246433

The simplest query would be, given that the type you look for is typschema.typname:

SELECT t.relnamespace::regnamespace AS schema,
       t.relname AS tablename
FROM pg_catalog.pg_class AS t
   JOIN pg_catalog.pg_attribute AS c
      ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;

Upvotes: 1

Related Questions