Goal Find in case of base tables, materialized views, and views the number of columns based on the length of the column name.
Type Sofware measure (Numeric values (software measures) about the database)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name, 
CASE WHEN relkind='r' THEN 'BASE TABLE' 
WHEN relkind='m' THEN 'MATERIALIZED VIEW' 
ELSE 'VIEW' END AS table_type, attname AS column_name
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
WHERE relkind IN ('r', 'm','v') AND attnum>=1 AND 
nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT table_type, length(column_name) AS length, Count(*) AS nr_of_occurrences, Round(Count(*)::decimal*100/(SELECT Count(*) FROM columns AS c2 WHERE c2.table_type=columns.table_type),1) AS percentage_of_all_columns
FROM columns
GROUP BY table_type, length(column_name)
ORDER BY table_type, length;

Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
NamingQueries of this category provide information about the style of naming.