This query calculates the total cardinality of semantically weak column identifiers across the entire schema, encompassing both base tables and views. It applies a uniform definition of "generic" (e.g., flagging identifiers like id, type, date) regardless of the underlying object type. By treating table and view columns equivalently, the query provides a holistic metric of naming ambiguity, quantifying the overall prevalence of non-descriptive attributes within the database's public interface.
Type
Sofware measure (Numeric values (software measures) about the database)
WITH columns AS (SELECT table_schema, table_name, table_type || ' COLUMN' AS suspected_object_type, column_name AS suspected_name, data_type
FROM information_schema.columns INNER JOIN information_schema.tables USING (table_schema, table_name)
UNION SELECT nspname, relname, 'MATERIALIZED VIEW COLUMN' AS type, attname, typname
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 INNER JOIN pg_type ON
pg_attribute.atttypid=pg_type.oid
WHERE relkind = 'm' AND attnum>=1)
SELECT translate(suspected_name,'01234567890_','') AS suspected_name, suspected_object_type, Count(*) AS nr_of_occurrences
FROM columns
WHERE
suspected_name~*'^[^[:alpha:]]*(id|tyyp|tuup|kood|aeg|kp|veerg|type|code|time|date|timestamp|fk|pk|value|info|data|number|expr|column|nimi|nimetus|kommentaar(id){0,1}|kirjeldus(ed){0,1}|staatus|seisund|olek|tyyp|tüüp|name(s){0,1}|comment(s){0,1}|description(s){0,1}|explanation(s){0,1}|state|status|type)[^[:alpha:]]*$'
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
GROUP BY ROLLUP (translate(suspected_name,'01234567890_','') , suspected_object_type)
ORDER BY suspected_name, nr_of_occurrences DESC, suspected_object_type;
Collections
This query belongs to the following collections:
Name
Description
Find problems about names
A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Find problems by overview
Queries 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:
Name
Description
Naming
Queries of this category provide information about the style of naming.