Query goal: | Find the names of tables that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic names like table, data, information, list etc. in the names of tables. |
Notes about the query: | The query considers both base tables and derived tables. 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 table. 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 tables AS (SELECT table_schema AS schema, table_schema AS container, table_type AS suspected_object_type, table_name AS suspected_name FROM information_schema.tables UNION SELECT nspname, nspname, 'MATERIALIZED VIEW' AS type, relname FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid WHERE relkind = 'm') SELECT schema, container, suspected_object_type, suspected_name FROM tables WHERE (suspected_name~*'(tabel|table|andme(?!baas)|data(?!base)|info|nimekiri|loetelu|list|subform)' OR suspected_name~*'^[^[:alpha:]]*(aruanne|aruanded|koondaruanne|koondaruanded|kommentaar(id){0,1}|seisund(id){0,1}|staatus(ed){0,1}|olek(ud){0,1}|tüüp|tüübid|tyyp|tyybid|tuup|tuubid|kategooria(d){0,1}|report(s){0,1}|comment(s){0,1}|state(s){0,1}|type(s){0,1}|category|categories|overview(s){0,1})[^[:alpha:]]*$' OR (suspected_name~*'(vaata|_vaade|vaade_|_view|view_|päring|paring|query|kuva|näita|display|show)' AND suspected_object_type IN ('VIEW', 'MATERIALIZED VIEW'))) AND container 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, container, 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. |
Reference |
---|
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N4: Unambiguous Names". (Robert C. Martin, Clean Code) |
The corresponding code problem in case of cleaning code is "Add Meaningful Context". (Robert C. Martin, Clean Code) |