Find the names of columns that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic words like data, information, column, etc. In the names of columns. Moreover, avoid too generic column names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk.
Notes
The query considers both names in English and in Estonian.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Add meaningful context to the name. Rename the column. Give to it more descriptive name. Follow a naming convention.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
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 table_schema, table_name, suspected_object_type, suspected_name, data_type AS column_type
FROM columns
WHERE (suspected_name~*'(andme(?!(baas|[_]*tyyp))|(?'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY suspected_object_type, table_schema, table_name, suspected_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 automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Naming
Queries of this category provide information about the style of naming.