Filter Queries

Found 1089 queries.

Page 14 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.

#261. Inconsistent naming of comment columns

INFORMATION_SCHEMA+system catalog base tables

Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments".

Problem detection License: MIT (opens in new tab)

#262. Potentially a classifier is missing (based on field sizes)

INFORMATION_SCHEMA+system catalog base tables

Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less.

General License: MIT (opens in new tab)

#263. Columns defined in a subtable

INFORMATION_SCHEMA+system catalog base tables

Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable.

General License: MIT (opens in new tab)

#264. Duplicate NOT NULL constraints

INFORMATION_SCHEMA+system catalog base tables

Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises.

Problem detection License: MIT (opens in new tab)

#265. Column name contains the table name

INFORMATION_SCHEMA+system catalog base tables

Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.

General License: MIT (opens in new tab)

#266. Column name is the same as the table name

INFORMATION_SCHEMA+system catalog base tables

Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent.

General License: MIT (opens in new tab)

#267. Perhaps the type of a base table column/domain should be SMALLINT (based on classifiers)

INFORMATION_SCHEMA only

Find columns that name points to the possibility that values in this are classifier codes. The column has a numeric type but it is not SMALLINT. Usually each classifier type has so few values that type SMALLINT would be appropriate.

Problem detection License: MIT (opens in new tab)

#268. Password is unique

INFORMATION_SCHEMA+system catalog base tables

Find columns that potentially contains passwords and that participate in a unique constraint or index

Problem detection License: MIT (opens in new tab)

#269. Storing file content in the database

INFORMATION_SCHEMA only

Find columns that probably store content of files in the database.

General License: MIT (opens in new tab)

#270. Precision of a timestamp or a time column is too big

INFORMATION_SCHEMA only

Find columns with a timestamp or a time type where the precision (the permitted maximum number of fractional sections) is bigger than the precision in the default value of the column.

Problem detection License: MIT (opens in new tab)

#271. Columns with BYTEA or OID type

INFORMATION_SCHEMA+system catalog base tables

Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type.

General License: MIT (opens in new tab)

#272. Columns with JSON, JSONB, or XML type

INFORMATION_SCHEMA+system catalog base tables

Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.

General License: MIT (opens in new tab)

#273. Comments of derived tables

system catalog base tables only

Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.

General License: MIT (opens in new tab)

#274. Comments of non-derived tables

system catalog base tables only

Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information.

General License: MIT (opens in new tab)

#275. Comments of routines

INFORMATION_SCHEMA+system catalog base tables

Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information.

General License: MIT (opens in new tab)

#276. Duplicate comments

INFORMATION_SCHEMA+system catalog base tables

Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing.

Problem detection License: MIT (opens in new tab)

#277. Completely overlapping foreign keys

INFORMATION_SCHEMA+system catalog base tables

Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys.

Problem detection License: MIT (opens in new tab)

#278. Duplicate keys

system catalog base tables only

Find completely overlapping key (primary key, unique, and exclude where all operators are =) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns.

Problem detection License: MIT (opens in new tab)

#279. Composite foreign keys with an incorrect order of columns (ver 2)

system catalog base tables only

Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.

Problem detection License: MIT (opens in new tab)

#280. Composite foreign keys with an incorrect order of columns (ver 1)

system catalog base tables only

Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.

Problem detection License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(sorted ascending, activate to sort descending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
261 Inconsistent naming of comment columns Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments". Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
262 Potentially a classifier is missing (based on field sizes) Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
263 Columns defined in a subtable Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
264 Duplicate NOT NULL constraints Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
265 Column name contains the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
266 Column name is the same as the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
267 Perhaps the type of a base table column/domain should be SMALLINT (based on classifiers) Find columns that name points to the possibility that values in this are classifier codes. The column has a numeric type but it is not SMALLINT. Usually each classifier type has so few values that type SMALLINT would be appropriate. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
268 Password is unique Find columns that potentially contains passwords and that participate in a unique constraint or index Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
269 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only MIT (opens in new tab) View
270 Precision of a timestamp or a time column is too big Find columns with a timestamp or a time type where the precision (the permitted maximum number of fractional sections) is bigger than the precision in the default value of the column. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
271 Columns with BYTEA or OID type Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
272 Columns with JSON, JSONB, or XML type Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
273 Comments of derived tables Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. General system catalog base tables only MIT (opens in new tab) View
274 Comments of non-derived tables Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information. General system catalog base tables only MIT (opens in new tab) View
275 Comments of routines Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
276 Duplicate comments Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
277 Completely overlapping foreign keys Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
278 Duplicate keys Find completely overlapping key (primary key, unique, and exclude where all operators are =) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns. Problem detection system catalog base tables only MIT (opens in new tab) View
279 Composite foreign keys with an incorrect order of columns (ver 2) Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. Problem detection system catalog base tables only MIT (opens in new tab) View
280 Composite foreign keys with an incorrect order of columns (ver 1) Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. Problem detection system catalog base tables only MIT (opens in new tab) View