Filter Queries

Found 1092 queries.

Page 15 of 55
  • 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.

#281. Do not clone columns

INFORMATION_SCHEMA only

"Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore).

Problem detection License: MIT (opens in new tab)

#282. Do not clone tables

INFORMATION_SCHEMA only

Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.).

Problem detection License: MIT (opens in new tab)

#283. Do not create multiple columns for the same attribute

INFORMATION_SCHEMA only

Find base tables that implement recording multivalued attribute values with the help of repeating group of columns. Find base tables that have more than one columns with the same type and field size and the difference between the columns are the numbers in the column names (column1, column2, etc.).

Problem detection License: MIT (opens in new tab)

#284. Do not create user-defined routines that have the same name as some installed extension routine

INFORMATION_SCHEMA+system catalog base tables

Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion.

Problem detection License: MIT (opens in new tab)

#285. Do not format comma-separated lists (based on column names)

INFORMATION_SCHEMA only

Find, based on column names, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Problem detection License: MIT (opens in new tab)

#286. Do not format comma-separated lists (based on default values)

INFORMATION_SCHEMA only

Find, based on default values, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Problem detection License: MIT (opens in new tab)

#287. Do not format comma-separated lists (based on user data)

INFORMATION_SCHEMA+system catalog base tables

Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.

Problem detection License: MIT (opens in new tab)

#288. Do not leave out referential constraints (based on composite keys)

INFORMATION_SCHEMA+system catalog base tables

Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints.

Problem detection License: MIT (opens in new tab)

#289. Do not leave out the referential constraints (based on adjacency list design)

INFORMATION_SCHEMA+system catalog base tables

Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers.

Problem detection License: MIT (opens in new tab)

#290. Do not leave out the referential constraints (based on classifiers)

INFORMATION_SCHEMA+system catalog base tables

This query identifies short text columns in base tables that are not part of any primary or foreign key, but whose names closely match an existing table in the database. This pattern suggests that the similarly named table might be a classifier (reference) table, and the flagged column should ideally have a foreign key constraint referencing it.

Problem detection License: MIT (opens in new tab)

#291. Do not leave out the referential constraints (based on column names)

INFORMATION_SCHEMA+system catalog base tables

This query identifies potentially missing foreign key constraints by analyzing base table columns whose names suggest they store reference codes or identifiers (IDs). It specifically flags columns that do not participate in any primary key, unique constraint, unique index, or foreign key constraint, and are not associated with a sequence generator (which would typically indicate a local surrogate key). Detecting these unconstrained reference columns helps prevent orphaned records and ensures relational data integrity across the schema.

Problem detection License: MIT (opens in new tab)

#292. Do not leave out the referential constraints (islands)

system catalog base tables only

Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema.

Problem detection License: MIT (opens in new tab)

#293. Do not leave out the referential constraints (pairs of tables)

INFORMATION_SCHEMA+system catalog base tables

Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship.

Problem detection License: MIT (opens in new tab)

#294. Do not refer to the table schema in the references to columns

INFORMATION_SCHEMA+system catalog base tables

Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code.

Problem detection License: MIT (opens in new tab)

#295. Do not specify a list of values in a table column definition

INFORMATION_SCHEMA+system catalog base tables

Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain.

Problem detection License: MIT (opens in new tab)

#296. Do not use a generic attribute table

INFORMATION_SCHEMA only

Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs.

Problem detection License: MIT (opens in new tab)

#297. Do not use approach that one size fits all (primary key columns)

INFORMATION_SCHEMA+system catalog base tables

Find base base tables have the simple primary key that contains the column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator.

Problem detection License: MIT (opens in new tab)

#298. Do not use approach that one size fits all (unique index columns)

INFORMATION_SCHEMA+system catalog base tables

Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator.

Problem detection License: MIT (opens in new tab)

#299. Do not use dual-purpose foreign keys

INFORMATION_SCHEMA only

Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row.

Problem detection License: MIT (opens in new tab)

#300. Do not use FLOAT Data Type

INFORMATION_SCHEMA only

Find base table columns that have FLOAT, REAL, or DOUBLE PRECISION type. "The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it." (PostgreSQL documentation) Do not use the approximate numeric types FLOAT, REAL, and DOUBLE PRECISION in order to present fractional numeric data. Due to the use of the IEEE 754 standard the results of calculations with the values, which have one of these types, can be inexact because out of necessity some numbers must be rounded to a value, which is very close. "Comparing two floating-point values for equality might not always work as expected." (PostgreSQL documentation)

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
281 Do not clone columns "Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore). Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
282 Do not clone tables Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.). Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
283 Do not create multiple columns for the same attribute Find base tables that implement recording multivalued attribute values with the help of repeating group of columns. Find base tables that have more than one columns with the same type and field size and the difference between the columns are the numbers in the column names (column1, column2, etc.). Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
284 Do not create user-defined routines that have the same name as some installed extension routine Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
285 Do not format comma-separated lists (based on column names) Find, based on column names, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
286 Do not format comma-separated lists (based on default values) Find, based on default values, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
287 Do not format comma-separated lists (based on user data) Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
288 Do not leave out referential constraints (based on composite keys) Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
289 Do not leave out the referential constraints (based on adjacency list design) Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
290 Do not leave out the referential constraints (based on classifiers) This query identifies short text columns in base tables that are not part of any primary or foreign key, but whose names closely match an existing table in the database. This pattern suggests that the similarly named table might be a classifier (reference) table, and the flagged column should ideally have a foreign key constraint referencing it. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
291 Do not leave out the referential constraints (based on column names) This query identifies potentially missing foreign key constraints by analyzing base table columns whose names suggest they store reference codes or identifiers (IDs). It specifically flags columns that do not participate in any primary key, unique constraint, unique index, or foreign key constraint, and are not associated with a sequence generator (which would typically indicate a local surrogate key). Detecting these unconstrained reference columns helps prevent orphaned records and ensures relational data integrity across the schema. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
292 Do not leave out the referential constraints (islands) Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema. Problem detection system catalog base tables only MIT (opens in new tab) View
293 Do not leave out the referential constraints (pairs of tables) Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
294 Do not refer to the table schema in the references to columns Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
295 Do not specify a list of values in a table column definition Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
296 Do not use a generic attribute table Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
297 Do not use approach that one size fits all (primary key columns) Find base base tables have the simple primary key that contains the column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
298 Do not use approach that one size fits all (unique index columns) Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
299 Do not use dual-purpose foreign keys Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
300 Do not use FLOAT Data Type Find base table columns that have FLOAT, REAL, or DOUBLE PRECISION type. "The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it." (PostgreSQL documentation) Do not use the approximate numeric types FLOAT, REAL, and DOUBLE PRECISION in order to present fractional numeric data. Due to the use of the IEEE 754 standard the results of calculations with the values, which have one of these types, can be inexact because out of necessity some numbers must be rounded to a value, which is very close. "Comparing two floating-point values for equality might not always work as expected." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View