I needed to find all tables in a specific schema that used json or jsonb data type columns. Here is my query, saved in a file called "find_cols.sql":
select col.table_schema, col.table_name, col.ordinal_position as column_id, col.column_name, col.data_type from information_schema.columns col join information_schema.tables tab on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE' where col.table_schema in ('myschema') and col.data_type IN ( 'json','jsonb' ) order by col.table_schema, col.table_name, col.ordinal_position;Execution in psql directly on server like this:
psql mydb -f find_cols.sql
No comments:
Post a Comment