Filter Queries

Found 11 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. All enumerated types

INFORMATION_SCHEMA+system catalog base tables

Find all enumerated types.

General License: MIT (opens in new tab)

#2. Base table columns with a composite type

system catalog base tables only

Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table.

General License: MIT (opens in new tab)

#3. Base tables created based on a type

system catalog base tables only

Find base tables that have been created based on a composite type and thnk through as to whether it was really needed.

General License: MIT (opens in new tab)

#4. Duplicate enumerated types

INFORMATION_SCHEMA+system catalog base tables

Find enumerated types with exactly the same values. There should not be multiple types that have the same values.

Problem detection License: MIT (opens in new tab)

#5. Duplicate independent (i.e., not created based on a table) composite types

system catalog base tables only

Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication.

Problem detection License: MIT (opens in new tab)

#6. Enumerated types with zero or one value

INFORMATION_SCHEMA+system catalog base tables

Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values.

Problem detection License: MIT (opens in new tab)

#7. Perhaps the type of a base table column should be BOOLEAN (based on enumerated types)

INFORMATION_SCHEMA+system catalog base tables

Find base table columns that have an enumerated type that seems to emulate Boolean type.

Problem detection License: MIT (opens in new tab)

#8. Too generic names (user-defined types, domains)

system catalog base tables only

Find names of user-defined types and domains that contain too generic (noise) words.

Problem detection License: MIT (opens in new tab)

#9. Unused composite types (for table columns, typed tables, input and output parameters)

system catalog base tables only

Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Problem detection License: MIT (opens in new tab)

#10. Unused enumerated types (for base table columns, domains, and parameters)

INFORMATION_SCHEMA+system catalog base tables

Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Problem detection License: MIT (opens in new tab)

#11. User-defined composite types

system catalog base tables only

Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation.

General License: MIT (opens in new tab)
# Name (sorted ascending) Goal Type Data source Last update License Actions
1 All enumerated types Find all enumerated types. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
2 Base table columns with a composite type Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table. General system catalog base tables only MIT (opens in new tab) View (opens in new tab)
3 Base tables created based on a type Find base tables that have been created based on a composite type and thnk through as to whether it was really needed. General system catalog base tables only MIT (opens in new tab) View (opens in new tab)
4 Duplicate enumerated types Find enumerated types with exactly the same values. There should not be multiple types that have the same values. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
5 Duplicate independent (i.e., not created based on a table) composite types Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
6 Enumerated types with zero or one value Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
7 Perhaps the type of a base table column should be BOOLEAN (based on enumerated types) Find base table columns that have an enumerated type that seems to emulate Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
8 Too generic names (user-defined types, domains) Find names of user-defined types and domains that contain too generic (noise) words. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
9 Unused composite types (for table columns, typed tables, input and output parameters) Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
10 Unused enumerated types (for base table columns, domains, and parameters) Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
11 User-defined composite types Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation. General system catalog base tables only MIT (opens in new tab) View (opens in new tab)