Goal The names should contain table name in order to make the names better understandable.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH active_db AS (SELECT 
t.tgname AS active_element_name,
n.nspname AS table_schema,
c.relname AS table_name,
'TRIGGER' AS type
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c,
pg_catalog.pg_trigger t
WHERE n.oid = c.relnamespace 
AND c.oid = t.tgrelid 
AND t.tgisinternal=FALSE
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION ALL SELECT 
r.rulename,
n.nspname AS schemaname,
c.relname AS tablename,
'RULE' AS type
FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class JOIN pg_namespace n ON n.oid = c.relnamespace 
WHERE r.rulename <> '_RETURN'::name
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT type, active_element_name AS suspected_active_element_name, table_schema, table_name
FROM active_db
WHERE active_element_name ILIKE '%' || table_name || '%'
OR translate(active_element_name,'_','') ILIKE '%' || translate(table_name,'_','') || '%'
ORDER BY table_schema, table_name, type, active_element_name;

Collections

This query belongs to the following collections:

NameDescription
Find problems about namesA 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 by overviewQueries 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:

NameDescription
NamingQueries of this category provide information about the style of naming.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.