Filter Queries

Found 1077 queries.

Page 27 of 54
  • 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.

#521. Names of character classes are not in the lowercase

INFORMATION_SCHEMA+system catalog base tables

Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]].

Problem detection License: MIT (opens in new tab)

#522. Names of columns with the type BOOLEAN

INFORMATION_SCHEMA+system catalog base tables

This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question.

General License: MIT (opens in new tab)

#523. Names of database objects (regular identifiers) that contain $

INFORMATION_SCHEMA+system catalog base tables

Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual)

Problem detection License: MIT (opens in new tab)

#524. Names of database objects that are fully uppercase

INFORMATION_SCHEMA+system catalog base tables

Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive.

Problem detection License: MIT (opens in new tab)

#525. Names of database objects that are used to manage the state of main objects in the database

INFORMATION_SCHEMA+system catalog base tables

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term.

General License: MIT (opens in new tab)

#526. Names of database objects that contain a digit

INFORMATION_SCHEMA+system catalog base tables

Find the names (identifiers) of user-defined database objects that contain at least one digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names.

General License: MIT (opens in new tab)

#527. Names of database objects that contain dollar sign

INFORMATION_SCHEMA+system catalog base tables

Find names of database objects that contain a dollar sign ($) that is not the first symbol of the name. In PostgreSQL regular identifiers cannot start with $. However, $ can be used in other positions of the name. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL documentation)

Problem detection License: MIT (opens in new tab)

#528. Names of database objects that end with an underscore

INFORMATION_SCHEMA+system catalog base tables

Find names of database objects that end with a underscore.

Problem detection License: MIT (opens in new tab)

#529. Names of database objects that mix snake_case and camelCase/PascalCase

INFORMATION_SCHEMA+system catalog base tables

Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive.

Problem detection License: MIT (opens in new tab)

#530. Names of database objects that perhaps end with a sequence number

INFORMATION_SCHEMA+system catalog base tables

This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute.

Problem detection License: MIT (opens in new tab)

#531. Names of database objects that perhaps end with a sequence number (2)

INFORMATION_SCHEMA+system catalog base tables

This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed.

Problem detection License: MIT (opens in new tab)

#532. Names of database objects that perhaps end with a sequence number (3)

INFORMATION_SCHEMA+system catalog base tables

This query identifies user-defined database objects that share a common container and a common base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2 in the same table or tables address1 and address2 in the same schema). Such a structure complicates querying (e.g., requiring checks across multiple columns or tables) and is difficult to scale. The correct approach is for example to create a separate table for the repeating attribute, establishing a one-to-many relationship with the parent table.

Problem detection License: MIT (opens in new tab)

#533. Names of database objects that perhaps end with a sequence number (aggregate view)

INFORMATION_SCHEMA+system catalog base tables

This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time.

Sofware measure License: MIT (opens in new tab)

#534. Names of database objects that start with an underscore

INFORMATION_SCHEMA+system catalog base tables

Improve the readability of names. Find the names (identifiers) of user-defined database objects that start with an underscore. This is not necessarily a mistake. For instance, parameter names could start with an underscore. On the other hand, it could be that the prefix is missing in the name.

General License: MIT (opens in new tab)

#535. Names of database objects with four or more consecutive identical symbols

INFORMATION_SCHEMA+system catalog base tables

Find names of database objects with four or more consecutive identical symbols

Problem detection License: MIT (opens in new tab)

#536. Names of database objects with perhaps too many digits

INFORMATION_SCHEMA+system catalog base tables

Find the names of database objects where more than half the signs are digits.

Problem detection License: MIT (opens in new tab)

#537. Names of database objects with perhaps too many subcomponents (terms)

INFORMATION_SCHEMA+system catalog base tables

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The number of subcomponents (terms) should not be too big. Find the names (identifiers) of user-defined database objects that perhaps contain too many subcomponents, assuming, that the separator of the components is "_".

Problem detection License: MIT (opens in new tab)

#538. Names of database objects with perhaps too many subcomponents (terms) that consist of only consonants

INFORMATION_SCHEMA+system catalog base tables

Find names of database objects where the number of subcomponents (terms) that consist of only consonants is bigger than the number of subcomponents that contain at least one vowel.

Problem detection License: MIT (opens in new tab)

#539. Names of the columns of derived tables that have been given by the system

INFORMATION_SCHEMA+system catalog base tables

Find columns of derived tables that name has been given by the system. The creators of the table should specify the name themselves to avoid ugly names and nasty surprises.

Problem detection License: MIT (opens in new tab)

#540. Names of the password columns

INFORMATION_SCHEMA+system catalog base tables

Find names of columns of base tables, views, and materialized views that contain passwords. Make sure that the naming is consistent,

General License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(sorted ascending, activate to sort descending) Last update(activate to sort ascending) License Actions
521 Names of character classes are not in the lowercase Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
522 Names of columns with the type BOOLEAN This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
523 Names of database objects (regular identifiers) that contain $ Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual) Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
524 Names of database objects that are fully uppercase Full uppercase means screaming and it makes comprehending the names more difficult. Find the names (identifiers) of user-defined database objects that are fully uppercase. Because PostgreSQL stores regular identifiers lowercase in the system catalog it also means that these are delimited identifiers, i.e., these are case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
525 Names of database objects that are used to manage the state of main objects in the database "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
526 Names of database objects that contain a digit Find the names (identifiers) of user-defined database objects that contain at least one digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
527 Names of database objects that contain dollar sign Find names of database objects that contain a dollar sign ($) that is not the first symbol of the name. In PostgreSQL regular identifiers cannot start with $. However, $ can be used in other positions of the name. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
528 Names of database objects that end with an underscore Find names of database objects that end with a underscore. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
529 Names of database objects that mix snake_case and camelCase/PascalCase Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
530 Names of database objects that perhaps end with a sequence number This query identifies user-defined database objects that share a common container and base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2). To avoid false positives—such as domains like d_name_50 and d_name_100 where the number signifies a length—the query employs a specific heuristic. It assumes a sequence starts with 1, 2, and 3. By removing these numbers from object names, it checks if multiple objects of the same type and base name result within the same container. A positive match strongly implies an intentional, sequential numbering. This pattern indicates a denormalized design, which complicates querying and is difficult to scale. The correct approach is to normalize the schema by creating a separate table for the repeating attribute. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
531 Names of database objects that perhaps end with a sequence number (2) This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
532 Names of database objects that perhaps end with a sequence number (3) This query identifies user-defined database objects that share a common container and a common base name, where the identifiers are distinguished solely by numerical suffixes (e.g., columns address1, address2 in the same table or tables address1 and address2 in the same schema). Such a structure complicates querying (e.g., requiring checks across multiple columns or tables) and is difficult to scale. The correct approach is for example to create a separate table for the repeating attribute, establishing a one-to-many relationship with the parent table. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
533 Names of database objects that perhaps end with a sequence number (aggregate view) This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
534 Names of database objects that start with an underscore Improve the readability of names. Find the names (identifiers) of user-defined database objects that start with an underscore. This is not necessarily a mistake. For instance, parameter names could start with an underscore. On the other hand, it could be that the prefix is missing in the name. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
535 Names of database objects with four or more consecutive identical symbols Find names of database objects with four or more consecutive identical symbols Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
536 Names of database objects with perhaps too many digits Find the names of database objects where more than half the signs are digits. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
537 Names of database objects with perhaps too many subcomponents (terms) "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) The number of subcomponents (terms) should not be too big. Find the names (identifiers) of user-defined database objects that perhaps contain too many subcomponents, assuming, that the separator of the components is "_". Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
538 Names of database objects with perhaps too many subcomponents (terms) that consist of only consonants Find names of database objects where the number of subcomponents (terms) that consist of only consonants is bigger than the number of subcomponents that contain at least one vowel. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
539 Names of the columns of derived tables that have been given by the system Find columns of derived tables that name has been given by the system. The creators of the table should specify the name themselves to avoid ugly names and nasty surprises. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
540 Names of the password columns Find names of columns of base tables, views, and materialized views that contain passwords. Make sure that the naming is consistent, General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View