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.
Type
General (Overview of some aspect of the database.)
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;
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
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.