Filter Queries

Found 1077 queries.

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

#1. All clustered indexes

system catalog base tables only

Find all tables that rows have been physically sorted based on an index.

General License: MIT (opens in new tab)

#2. All database privileges

system catalog base tables only

Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.

General License: MIT (opens in new tab)

#3. All event triggers

system catalog base tables only

Find event triggers, which are not associated to a specific schema object.

General License: MIT (opens in new tab)

#4. All exclude constraints

system catalog base tables only

Find all exclude constraints.

General License: MIT (opens in new tab)

#5. All foreign key constraints

system catalog base tables only

Enforce referential integrity in database. Find all referential integrity (foreign key) constraints.

General License: MIT (opens in new tab)

#6. All identifying relationships

system catalog base tables only

Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).

General License: MIT (opens in new tab)

#7. All key constraints

system catalog base tables only

This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.

General License: MIT (opens in new tab)

#8. All non-identifying relationships

system catalog base tables only

Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).

General License: MIT (opens in new tab)

#9. All rules

system catalog base tables only

Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint.

General License: MIT (opens in new tab)

#10. All security policies

system catalog base tables only

Find all security policies.

General License: MIT (opens in new tab)

#11. All supertables

system catalog base tables only

Find all the base tables that serve as supertables in the inheritance hierarchies

Sofware measure License: MIT (opens in new tab)

#12. All system-defined TOAST-able types

system catalog base tables only

Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).

General License: MIT (opens in new tab)

#13. All uniqueness constraints where nulls are not distinct

system catalog base tables only

This query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions

General License: MIT (opens in new tab)

#14. All user-defined TOAST-able types

system catalog base tables only

Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).

General License: MIT (opens in new tab)

#15. All user mappings

system catalog base tables only

Find all user mappings for foreign servers and tables

General License: MIT (opens in new tab)

#16. A state machine is implemented with the help of a state classifier table

system catalog base tables only

Find implementations of state machines that use a state classifier table.

General License: MIT (opens in new tab)

#17. Base table columns where TOASTing is possible

system catalog base tables only

Find all base table columns in case of which the system can use TOAST technique.

General License: MIT (opens in new tab)

#18. Base table columns where TOAST-ing strategy has been changed

system catalog base tables only

Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal.

General License: MIT (opens in new tab)

#19. Base table columns where TOAST-ing strategy has been changed to plain

system catalog base tables only

Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row.

Problem detection License: MIT (opens in new tab)

#20. Base table columns with a composite type

system catalog base tables only

Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table.

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 descending, activate to sort ascending) Last update(activate to sort ascending) License Actions
1 All clustered indexes Find all tables that rows have been physically sorted based on an index. General system catalog base tables only MIT (opens in new tab) View
2 All database privileges Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY. General system catalog base tables only MIT (opens in new tab) View
3 All event triggers Find event triggers, which are not associated to a specific schema object. General system catalog base tables only MIT (opens in new tab) View
4 All exclude constraints Find all exclude constraints. General system catalog base tables only MIT (opens in new tab) View
5 All foreign key constraints Enforce referential integrity in database. Find all referential integrity (foreign key) constraints. General system catalog base tables only MIT (opens in new tab) View
6 All identifying relationships Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint). General system catalog base tables only MIT (opens in new tab) View
7 All key constraints This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table. General system catalog base tables only MIT (opens in new tab) View
8 All non-identifying relationships Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint). General system catalog base tables only MIT (opens in new tab) View
9 All rules Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint. General system catalog base tables only MIT (opens in new tab) View
10 All security policies Find all security policies. General system catalog base tables only MIT (opens in new tab) View
11 All supertables Find all the base tables that serve as supertables in the inheritance hierarchies Sofware measure system catalog base tables only MIT (opens in new tab) View
12 All system-defined TOAST-able types Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only MIT (opens in new tab) View
13 All uniqueness constraints where nulls are not distinct This query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions General system catalog base tables only MIT (opens in new tab) View
14 All user-defined TOAST-able types Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only MIT (opens in new tab) View
15 All user mappings Find all user mappings for foreign servers and tables General system catalog base tables only MIT (opens in new tab) View
16 A state machine is implemented with the help of a state classifier table Find implementations of state machines that use a state classifier table. General system catalog base tables only MIT (opens in new tab) View
17 Base table columns where TOASTing is possible Find all base table columns in case of which the system can use TOAST technique. General system catalog base tables only MIT (opens in new tab) View
18 Base table columns where TOAST-ing strategy has been changed Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal. General system catalog base tables only MIT (opens in new tab) View
19 Base table columns where TOAST-ing strategy has been changed to plain Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row. Problem detection system catalog base tables only MIT (opens in new tab) View
20 Base table columns with a composite type Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table. General system catalog base tables only MIT (opens in new tab) View