Find all tables with a given column name (e.g. `is_removed`)

find_tables_with_is_removed.sql

select t.table_schema,
       t.table_name,
       CASE WHEN EXISTS(SELECT *
       FROM information_schema.columns c 
       WHERE c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
                                and c.column_name = t.table_name || '_id'
       ) THEN '✅' ELSE '❌' END
 AS has_matching_id_column,
       (SELECT c.column_name
       FROM information_schema.columns c 
       WHERE c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
                                and c.column_name LIKE '%_id'
		LIMIT 1
		       )
 AS id_column,
	       CASE WHEN EXISTS(SELECT *
       FROM information_schema.columns c
       WHERE c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
                                and c.column_name = t.table_name || '_version'
       )  THEN '✅' ELSE '❌' END
AS has_matching_version_column,
       
	       CASE WHEN EXISTS(SELECT *
       FROM information_schema.columns c
       WHERE c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
                                and c.column_name = 'version_by'
       )  THEN '✅' ELSE '❌' END
AS has_version_by_column
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'is_removed'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
      AND t.table_name NOT LIKE '%_history'
order by t.table_schema;