Filter Queries

Found 997 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.

# Name Goal Type Data source Last update License
881 Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application. Problem detection system catalog base tables only 2021-12-31 14:40 MIT License View
882 Exclude constraint instead of simple UNIQUE Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
883 Extension routines Find all routines that belong to an extension. General system catalog base tables only 2020-11-06 14:51 MIT License View
884 Extension routines in the schema "public" Find extensions that routines are in the schema public. Problem detection system catalog base tables only 2025-06-27 13:01 MIT License View
885 Extensions that are available but are not installed Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. General system catalog base tables only 2020-11-06 14:51 MIT License View
886 Foreign key column has a simple check constraint that is attached directly to the table Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
887 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
888 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
889 Foreign key references to a unique index columns not a unique key columns Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. Problem detection system catalog base tables only 2022-10-21 10:30 MIT License View
890 Foreign key refers to a table that has at least one subtable in the inheritance hierarchy Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
891 Foreign keys with ON DELETE CASCADE Find referential constraints (foreign key constraints) that employ ON DELETE CASCADE compensatory action. ON DELETE CASCADE should only be used if it has been created based on a generalization or a composition in the conceptual data model or if the foreign key connects a table that corresponds to the main entity type with a table that corresponds to a non-main entity type. General system catalog base tables only 2020-11-16 10:15 MIT License View
892 Foreign keys with ON UPDATE CASCADE Referential constraints (foreign key constraints) that employ ON UPDATE CASCADE compensatory action. ON UPDATE CASCADE should only be used if the referenced key is a natural key (its values can be changed). General system catalog base tables only 2020-11-15 15:39 MIT License View
893 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
894 Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only 2024-01-07 13:43 MIT License View
895 Grantable roles Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only 2024-01-07 13:42 MIT License View
896 Granted roles Find membership relations between roles. General system catalog base tables only 2024-01-07 13:30 MIT License View
897 Gratuitous context in the names of foreign key columns Find foreign key columns that name contains twice the name of the referenced (primary) table. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
898 Gratuitous context in the names of schema objects Find schema objects that name starts with the schema name and then has at least one more symbol. "Shorter names are generally better than longer ones, so long as they are clear. Add no more context to a name than is necessary" (Robert C. Martin, Clean Code) Shema is a namespace. There cannot be in the same schema two schema objects that belong to the same name class and have the same name. Problem detection system catalog base tables only 2024-12-21 17:30 MIT License View
899 Identical indexes Find indexes that are identical, i.e., have the same properties, including uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
900 Inconsistency (code vs. id) of naming foreign key and referenced candidate key columns Naming of foreign key and referenced candidate key columns should be consistent. It cannot be so that in one table a value is labeled "id" like some surrogate key value and in another it "turns" into human-usable "code" or vice versa. An example:

Person(person_id, name)
Primary Key (person_id)

E_mail_address(e_mail_address_id, person_code, address)
Primary Key (e_mail_address_id)
Foreign key (person_code) References Person (person_id)
Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View