sql.columns_without_description.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of pg-index-health-core Show documentation

pg-index-health-core is a Java library for analyzing and maintaining indexes and tables health in PostgreSQL databases on a specific host.

The newest version!

/*
 * Copyright (c) 2019-2024. Ivan Vakhrushev and others.
 * https://github.com/mfvanek/pg-index-health-sql
 *
 * Licensed under the Apache License 2.0
 */

-- Finds columns that don't have a description. See also https://www.postgresql.org/docs/current/sql-comment.html
select
    t.oid::regclass::text as table_name,
    col.attname::text as column_name,
    col.attnotnull as column_not_null
from
    pg_catalog.pg_class t
    inner join pg_catalog.pg_namespace nsp on nsp.oid = t.relnamespace
    inner join pg_catalog.pg_attribute col on col.attrelid = t.oid
where
    t.relkind = 'r' and
    col.attnum > 0 and /* to filter out system columns such as oid, ctid, xmin, xmax, etc. */
    not col.attisdropped and
    (col_description(t.oid, col.attnum) is null or length(trim(col_description(t.oid, col.attnum))) = 0) and
    nsp.nspname = :schema_name_param::text
order by table_name, column_name;