What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables.
Type
Problem detection (Each row in the result could represent a flaw in the design)
If there is data redundancy, then it must be controlled. One can, for instance, achieve controlled data redundancy with the help of triggers.
Data Source
INFORMATION_SCHEMA only
SQL Query
WITH tables AS (SELECT C.table_schema, C.table_name, C.column_name, C.data_type || coalesce(C.character_maximum_length::text, C.numeric_precision ||'.'|| C.numeric_scale, '0') AS data_type
FROM information_schema.columns C INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON T.table_schema = S.schema_name
WHERE T.table_type = 'BASE TABLE' AND (T.table_schema = 'public' OR S.schema_owner<>'postgres'))
SELECT T1.table_schema AS T1_schema, T1.table_name AS T1_name, T2.table_schema AS T2_schema, T2.table_name AS T2_name, Count(*) AS number_of_common_columns
FROM tables AS T1, tables AS T2
WHERE T1.column_name=T2.column_name AND T1.data_type=T2.data_type
AND (T1.table_name>T2.table_name)
GROUP BY T1.table_schema, T1.table_name, T2.table_schema, T2.table_name
HAVING Count(*)>1
ORDER BY number_of_common_columns DESC, T1.table_schema, T1.table_name, T2.table_schema, T2.table_name;
Categories
This query is classified under the following categories:
Name
Description
Data redundancy
Queries of this category provide information about possible data redunancy.
Data types
Queries of this category provide information about the data types and their usage.
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.