Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.
Notes
In the column name, a part that is the same as the table name, is between (b) tags for the better readability in case the query result is displayed in a web browser. The query considers both column names in English and Estonian. A column belongs to a candidate key if it is covered by a PRIMARY KEY or UNIQUE constraint.
Type
General (Overview of some aspect of the database.)
If the naming is inconsistent, then make it consistent.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH key_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u','p')) t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false),
fk_columns AS (
select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f') t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false
)
SELECT table_schema, table_name, regexp_replace(column_name, table_name, '' || table_name || '','g') AS column_name,
substring(column_name from '(nimi|nimetus|kirjeldus|kommentaar|selgitus|lugu|pealkiri|klass|name|comment|description|explanation|story|title|class)$') as generic_column_name
FROM INFORMATION_SCHEMA.columns AS c
WHERE column_name~*'(nimi|nimetus|kirjeldus|kommentaar|selgitus|lugu|pealkiri|klass|name|comment|description|explanation|story|title|class)$'
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') 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) AND
NOT EXISTS (SELECT 1 FROM key_columns AS pk WHERE pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name) AND
NOT EXISTS (SELECT 1 FROM fk_columns AS fk WHERE fk.table_schema=c.table_schema AND fk.table_name=c.table_name AND fk.column_name=c.column_name)
ORDER BY generic_column_name, column_name, table_schema, table_name;
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 .
Lexicon bad smells and linguistic antipatterns
Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.
Further reading and related materials:
Reference
The corresponding code smell in case of cleaning code is "N4: Unambiguous Names". (Robert C. Martin, Clean Code)
Smell "Whole-part": Abebe, S.L., Haiduc, S., Tonella, P. and Marcus, A., 2011, September. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.