Filter Queries

Found 3 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

#1. Duplicate materialized views

system catalog base tables only

Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Problem detection License: MIT (opens in new tab)

#2. Pairs of base tables that have at least two columns with the same names and data types

INFORMATION_SCHEMA only

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.

Problem detection License: MIT (opens in new tab)

#3. Tables with the same name in different schemas

INFORMATION_SCHEMA+system catalog base tables

Find tables with the same name in different schemas. Make sure that this is not a duplication.

General License: MIT (opens in new tab)
# Name (sorted ascending) Goal Type Data source Last update License Actions
1 Duplicate materialized views Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
2 Pairs of base tables that have at least two columns with the same names and data types 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. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View (opens in new tab)
3 Tables with the same name in different schemas Find tables with the same name in different schemas. Make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)