Query goal: | 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 about the query: | The query considers both names in English and in Estonian. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | 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: | Click on query to copy it
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))|(?<!eel)data(?!(base|[_]*type))|info|veerg|column)' OR suspected_name~*'^[^[:alpha:]]*(id|tyyp|tuup|kood|aeg|kp|type|code|time|date|fk|pk|value|info|data|number|expr)[^[:alpha:]]*$' OR (suspected_object_type ILIKE '%VIEW%' AND suspected_name~*'^[^[:alpha:]]*(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) ORDER BY suspected_object_type, table_schema, table_name, suspected_name; |
Collection name | Collection 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 . |
Category name | Category 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. |