Filter Queries

Found 68 queries.

Page 1 of 4
  • 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 the relatively small amount of data in the system catalog of a database.

#1. All column DEFAULT values

INFORMATION_SCHEMA only

Find all the default values of base table, view, and foreign table columns.

General License: MIT (opens in new tab)

#2. All column dynamic DEFAULT values values that do not invoke a sequence

INFORMATION_SCHEMA only

Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence.

General License: MIT (opens in new tab)

#3. All columns of a base table have a default value

INFORMATION_SCHEMA only

Find base tables where all the columns have a default value.

Problem detection License: MIT (opens in new tab)

#4. All column static DEFAULT values

INFORMATION_SCHEMA only

Find all columns that have a static default value, i.e., the value is not returned by a function.

General License: MIT (opens in new tab)

#5. All domain default values

INFORMATION_SCHEMA only

Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values.

General License: MIT (opens in new tab)

#6. All parameters with DEFAULT values

INFORMATION_SCHEMA+system catalog base tables

Find parameters of user-defined routines that have a default value.

General License: MIT (opens in new tab)

#7. Base tables that have a surrogate key and all its unique constraints have an optional column

INFORMATION_SCHEMA+system catalog base tables

This query identifies tables with a surrogate primary key (an auto-generated, meaningless single-column identifier) that either lack a natural key entirely or have a flawed natural key constraint. A common design mistake when introducing a surrogate key is forgetting to enforce uniqueness on the actual business identifiers (natural keys). Furthermore, if a UNIQUE constraint includes an optional (nullable) column, it fails to prevent duplicate rows. Because NULL evaluates to an unknown state and is not considered equal to another NULL in standard SQL, multiple rows with identical values and NULLs can be inserted. The query flags these vulnerabilities unless the constraint explicitly uses the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal.

Problem detection License: MIT (opens in new tab)

#8. Base tables that have a surrogate key and do not have any uniqueness constraints

INFORMATION_SCHEMA+system catalog base tables

This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check.

Problem detection License: MIT (opens in new tab)

#9. Base tables that have only the surrogate key and do not have any other column

INFORMATION_SCHEMA only

Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values.

Problem detection License: MIT (opens in new tab)

#10. Boolean state columns missing default values

INFORMATION_SCHEMA only

This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created.

Problem detection License: MIT (opens in new tab)

#11. Candidate key columns that have a static default value

INFORMATION_SCHEMA+system catalog base tables

Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value.

Problem detection License: MIT (opens in new tab)

#12. Candidate keys where all columns have a static default value

INFORMATION_SCHEMA+system catalog base tables

Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value.

Problem detection License: MIT (opens in new tab)

#13. Cannot accommodate all the fractional seconds in case of table columns

INFORMATION_SCHEMA only

The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m.

Problem detection License: MIT (opens in new tab)

#14. CHAR columns have a default value that length is shorter from the character maximum length of the column

INFORMATION_SCHEMA only

Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value.

Problem detection License: MIT (opens in new tab)

#15. CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column

INFORMATION_SCHEMA only

Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces).

Problem detection License: MIT (opens in new tab)

#16. CHECK constraints are inconsistent with DEFAULT values

INFORMATION_SCHEMA+system catalog base tables

This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice.

Problem detection License: MIT (opens in new tab)

#17. CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values

INFORMATION_SCHEMA+system catalog base tables

Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION.

Problem detection License: MIT (opens in new tab)

#18. Columns for registration and update times

INFORMATION_SCHEMA only

Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties.

General License: MIT (opens in new tab)

#19. Columns of base tables that hold truth values but do not have a default value (Boolean columns)

INFORMATION_SCHEMA only

Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type.

Problem detection License: MIT (opens in new tab)

#20. Columns of base tables that hold truth values but do not have a default value (non-Boolean columns)

INFORMATION_SCHEMA+system catalog base tables

Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns.

Problem detection License: MIT (opens in new tab)
# Name(sorted ascending, activate to sort descending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
1 All column DEFAULT values Find all the default values of base table, view, and foreign table columns. General INFORMATION_SCHEMA only MIT (opens in new tab) View
2 All column dynamic DEFAULT values values that do not invoke a sequence Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence. General INFORMATION_SCHEMA only MIT (opens in new tab) View
3 All columns of a base table have a default value Find base tables where all the columns have a default value. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
4 All column static DEFAULT values Find all columns that have a static default value, i.e., the value is not returned by a function. General INFORMATION_SCHEMA only MIT (opens in new tab) View
5 All domain default values Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values. General INFORMATION_SCHEMA only MIT (opens in new tab) View
6 All parameters with DEFAULT values Find parameters of user-defined routines that have a default value. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
7 Base tables that have a surrogate key and all its unique constraints have an optional column This query identifies tables with a surrogate primary key (an auto-generated, meaningless single-column identifier) that either lack a natural key entirely or have a flawed natural key constraint. A common design mistake when introducing a surrogate key is forgetting to enforce uniqueness on the actual business identifiers (natural keys). Furthermore, if a UNIQUE constraint includes an optional (nullable) column, it fails to prevent duplicate rows. Because NULL evaluates to an unknown state and is not considered equal to another NULL in standard SQL, multiple rows with identical values and NULLs can be inserted. The query flags these vulnerabilities unless the constraint explicitly uses the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
8 Base tables that have a surrogate key and do not have any uniqueness constraints This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
9 Base tables that have only the surrogate key and do not have any other column Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
10 Boolean state columns missing default values This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
11 Candidate key columns that have a static default value Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
12 Candidate keys where all columns have a static default value Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
13 Cannot accommodate all the fractional seconds in case of table columns The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
14 CHAR columns have a default value that length is shorter from the character maximum length of the column Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
15 CHAR or VARCHAR columns have a default value that length is longer from the character maximum length of the column Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces). Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
16 CHECK constraints are inconsistent with DEFAULT values This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
17 CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
18 Columns for registration and update times Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties. General INFORMATION_SCHEMA only MIT (opens in new tab) View
19 Columns of base tables that hold truth values but do not have a default value (Boolean columns) Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
20 Columns of base tables that hold truth values but do not have a default value (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View