Filter Queries

Found 1077 queries.

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

#761. The number of routines by the number of input parameters

INFORMATION_SCHEMA+system catalog base tables

Find the number of user-defined non-trigger routines by the number of input parameters.

Sofware measure License: MIT (opens in new tab)

#762. The number of SQL-language routines with different types of bodies

INFORMATION_SCHEMA+system catalog base tables

Find for each user schema that contain a SQL-language routine how many of these have the body that is string literal and how many have the body (possible starting from PostgreSQL 14) that is parsed at the routine definition time. Try to be consistent, i.e., use the same solution in case of all the routines.

Sofware measure License: MIT (opens in new tab)

#763. The number of tables based on the number of columns

INFORMATION_SCHEMA+system catalog base tables

Find how many tables with a certain range of the number of columns there are in the database.

Sofware measure License: MIT (opens in new tab)

#764. The number of tables by schema, by type, and in total

INFORMATION_SCHEMA+system catalog base tables

Find the number of tables (base, foreign, and derived) in different schemas.

Sofware measure License: MIT (opens in new tab)

#765. The number of user-defined non-trigger routines by schema, by language, and by routine type

INFORMATION_SCHEMA+system catalog base tables

Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.

Sofware measure License: MIT (opens in new tab)

#766. The number of user-defined non-trigger routines by schema, by security type, and by being deterministic

INFORMATION_SCHEMA+system catalog base tables

Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer.

Sofware measure License: MIT (opens in new tab)

#767. The number of user-defined routines based on their database usage

INFORMATION_SCHEMA+system catalog base tables

Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data.

Sofware measure License: MIT (opens in new tab)

#768. The number of user-defined triggers

INFORMATION_SCHEMA+system catalog base tables

Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers.

Sofware measure License: MIT (opens in new tab)

#769. The number of user-defined triggers by schema, by type, and in total

INFORMATION_SCHEMA+system catalog base tables

Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level.

Sofware measure License: MIT (opens in new tab)

#770. The reference to a database operation is missing from a comment

INFORMATION_SCHEMA+system catalog base tables

This query audits the metadata of user-defined routines to enforce traceability between the implementation and the design specifications. It identifies routines whose comments lack a standardized reference to the specific database operation contract they implement. The query checks for the absence of a required identifier pattern, typically formatted as OP followed by a number (e.g., OP1, OP12). Enforcing this standard ensures that every routine can be mapped back to its originating requirement or business rule.

Problem detection License: MIT (opens in new tab)

#771. There is no reason to use PL/pgSQL if you do not use one or more features of a procedural language

INFORMATION_SCHEMA+system catalog base tables

Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs (variables, conditional statements, cycles, cursors, exception handling).

Problem detection License: MIT (opens in new tab)

#772. There is no reason to use PL/pgSQL to write table functions

INFORMATION_SCHEMA+system catalog base tables

Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL.

Problem detection License: MIT (opens in new tab)

#773. The same database object name is used repeatedly in case the same database object type

INFORMATION_SCHEMA+system catalog base tables

Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database.

General License: MIT (opens in new tab)

#774. The same name is used in different contexsts

INFORMATION_SCHEMA+system catalog base tables

Find the names that are used in case of different types of elements

Problem detection License: MIT (opens in new tab)

#775. The shortest names of database objects by object type

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) Names should be expressive. Find the shortest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names.

General License: MIT (opens in new tab)

#776. The SQL-language routines with the body that is string literal

INFORMATION_SCHEMA+system catalog base tables

Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14.

Problem detection License: MIT (opens in new tab)

#777. The total number of physical lines of code in the bodies of user-defined SQL and PL/pgSQL functions/procedures

INFORMATION_SCHEMA+system catalog base tables

Find a numerical measure that describes the extent of the database public interface (virtual data layer) through which users access the database.

Sofware measure License: MIT (opens in new tab)

#778. The usage of data type formatting functions

INFORMATION_SCHEMA+system catalog base tables

Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp.

General License: MIT (opens in new tab)

#779. The usage of double vs singular underscores or spaces in names as separator of name components

INFORMATION_SCHEMA+system catalog base tables

Improve the readability of names. Find the number of names (identifiers) of user-defined database objects that contain two or more consecutive underscores or spaces as the separator of name components vs. the number of names that contain a single underscore or space to separate name components. Try to be consistent in the usage of underscores.

Sofware measure License: MIT (opens in new tab)

#780. The use of xmin hidden column in views and routines

INFORMATION_SCHEMA+system catalog base tables

Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows.

Sofware measure 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
761 The number of routines by the number of input parameters Find the number of user-defined non-trigger routines by the number of input parameters. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
762 The number of SQL-language routines with different types of bodies Find for each user schema that contain a SQL-language routine how many of these have the body that is string literal and how many have the body (possible starting from PostgreSQL 14) that is parsed at the routine definition time. Try to be consistent, i.e., use the same solution in case of all the routines. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
763 The number of tables based on the number of columns Find how many tables with a certain range of the number of columns there are in the database. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
764 The number of tables by schema, by type, and in total Find the number of tables (base, foreign, and derived) in different schemas. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
765 The number of user-defined non-trigger routines by schema, by language, and by routine type Find the number of user-defined non-trigger routines based on their schema, language, and routine type. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
766 The number of user-defined non-trigger routines by schema, by security type, and by being deterministic Find the number of user-defined non-trigger routines based on their schema, security type, and being deterministic. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
767 The number of user-defined routines based on their database usage Find the number of non-extension routines in a database that modify data. The routines can be used to implement virtual data layer. Thus the queriy gives some indications about the possible extent of the layer. The query distinguishes trigger and non-trigger routines, thus it also gives information about triggers that modify data. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
768 The number of user-defined triggers Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
769 The number of user-defined triggers by schema, by type, and in total Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
770 The reference to a database operation is missing from a comment This query audits the metadata of user-defined routines to enforce traceability between the implementation and the design specifications. It identifies routines whose comments lack a standardized reference to the specific database operation contract they implement. The query checks for the absence of a required identifier pattern, typically formatted as OP followed by a number (e.g., OP1, OP12). Enforcing this standard ensures that every routine can be mapped back to its originating requirement or business rule. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
771 There is no reason to use PL/pgSQL if you do not use one or more features of a procedural language Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs (variables, conditional statements, cycles, cursors, exception handling). Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
772 There is no reason to use PL/pgSQL to write table functions Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
773 The same database object name is used repeatedly in case the same database object type Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
774 The same name is used in different contexsts Find the names that are used in case of different types of elements Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
775 The shortest names of database objects by object type "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) Names should be expressive. Find the shortest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
776 The SQL-language routines with the body that is string literal Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
777 The total number of physical lines of code in the bodies of user-defined SQL and PL/pgSQL functions/procedures Find a numerical measure that describes the extent of the database public interface (virtual data layer) through which users access the database. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
778 The usage of data type formatting functions Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
779 The usage of double vs singular underscores or spaces in names as separator of name components Improve the readability of names. Find the number of names (identifiers) of user-defined database objects that contain two or more consecutive underscores or spaces as the separator of name components vs. the number of names that contain a single underscore or space to separate name components. Try to be consistent in the usage of underscores. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
780 The use of xmin hidden column in views and routines Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View