Find missing comments

D

Did you even wonder if you have commnets on all your tables/views and columns?

WITH o AS (
    SELECT o.object_type, o.object_name
    FROM user_objects o
    WHERE o.object_type IN ('TABLE', 'VIEW')
),
r AS (
    SELECT o.object_type, o.object_name, NULL AS column_name
    FROM o
    JOIN user_tab_comments c
        ON c.table_name     = o.object_name
    WHERE c.comments IS NULL
    UNION ALL
    SELECT o.object_type, o.object_name, c.column_name
    FROM o
    JOIN user_col_comments c
        ON c.table_name     = o.object_name
    WHERE c.comments IS NULL
)
SELECT
    r.object_type,
    r.object_name,
    MAX(CASE WHEN r.column_name IS NULL     THEN 1 END)                     AS table_missing,
    NULLIF(SUM(CASE WHEN r.column_name IS NOT NULL THEN 1 ELSE 0 END), 0)   AS columns_missing
FROM r
GROUP BY r.object_type, r.object_name
ORDER BY 2;