Query goal: | Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT A.table_schema, A.table_name , A.column_name, A.data_type, A.datetime_precision, A.is_nullable, A.column_default, D.domain_default FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name LEFT JOIN information_schema.domains D USING (domain_schema, domain_name) WHERE A.data_type~*'(timestamp|date)' AND (A.column_name~*'(reg|creat|insert|loom|lisam)' OR (A.column_name~*'(muutm|update|change|modify)' AND A.column_name!~*'(jargmine|next)')) AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY table_schema, table_name, column_name; |
Collection name | Collection description |
---|---|
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 . |
Category name | Category description |
---|---|
Default value | Queries of this catergory provide information about the use of default values. |
Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
Temporal data | Queries of this category provide information about temporal (time-related) data that is kept in the database. |