Find database object that do not belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make the naming style inconsistent with the naming style of elements that belong to the public interface. If applications access base tables directly, then the letters can cause the same problems as in case of derived tables, i.e., applications may have difficulties with such names.
Notes
In case or in addition to the Estonian letters one can use additional letters.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the database objects and remove the letters in question.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schema, suspected_name, container, type
FROM (
SELECT table_schema AS schema, table_name AS suspected_name, table_schema AS container, table_type AS type
FROM information_schema.tables
WHERE table_type='BASE TABLE' AND table_name~'[õäöüÕÄÖÜ]'
UNION SELECT domain_schema AS schema, domain_name AS suspected_name, domain_schema AS container, 'DOMAIN' AS type
FROM information_schema.domains
WHERE domain_name~'[õäöüÕÄÖÜ]'
UNION SELECT sequence_schema AS schema, sequence_name AS suspected_name, sequence_schema AS container, 'SEQUENCE' AS type
FROM information_schema.sequences
WHERE sequence_name~'[õäöüÕÄÖÜ]'
UNION SELECT table_schema, column_name, table_name, 'BASE TABLE COLUMN' AS type
FROM information_schema.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND column_name~'[õäöüÕÄÖÜ]'
UNION select
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
o.conname as constraint_name,
c.relname as table_name,
case when o.contype='p' then 'PRIMARY KEY'
when o.contype='u' then 'UNIQUE'
when o.contype='f' then 'FOREIGN KEY'
when o.contype='c' then 'TABLE CHECK'
when o.contype='x' then 'EXCLUDE'
when o.contype='t' then 'CONSTRAINT TRIGGER' END AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where 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) AND conname~'[õäöüÕÄÖÜ]'
UNION SELECT domain_schema, constraint_name, domain_name, 'DOMAIN CHECK' AS type
FROM INFORMATION_SCHEMA.domain_constraints
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND constraint_name~'[õäöüÕÄÖÜ]'
UNION SELECT trigger_schema, trigger_name, trigger_schema, 'TRIGGER' AS type
FROM information_schema.triggers
WHERE trigger_name~'[õäöüÕÄÖÜ]'
UNION SELECT nspname, relname, nspname, 'INDEX' AS type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
WHERE relkind = 'i' AND relname~'[õäöüÕÄÖÜ]') AS foo
WHERE 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 type, schema, suspected_name;
Collections
This query belongs to the following collections:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.