Skip to content

Instantly share code, notes, and snippets.

@smerchek

smerchek/find_tables_with_is_removed.sql

Last active July 9, 2019 16:13
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save smerchek/93d265b6feb95311321a7e4834bd256f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save smerchek/93d265b6feb95311321a7e4834bd256f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. smerchek revised this gist Jul 9, 2019. 1 changed file with 35 additions and 2 deletions.
    37 changes: 35 additions & 2 deletions find_tables_with_is_removed.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,42 @@
    select t.table_schema,
    t.table_name
    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'
    order by t.table_schema;
    AND t.table_name NOT LIKE '%_history'
    order by t.table_schema;


  2. smerchek created this gist Jun 26, 2019.
    9 changes: 9 additions & 0 deletions find_tables_with_is_removed.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,9 @@
    select t.table_schema,
    t.table_name
    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'
    order by t.table_schema;