Goal Identify the number of sequence generators in different schemas. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys at the database level. An alternative is to implement generation of unique values at the application level or at the database level by using triggers. However, such implementation would most probably lead to the performance penalty because adding new rows to the table must be serialized, i.e., it can be done in one session at a time.
Notes The query considers both external sequence generators (are produced by executing CREATE SEQUENCE statement or by using SMALLSERIAL/SERIAL/BIGSERIAL notation) as well as internal sequence generators (are created for identity columns). The query only returns data about schemas that have at least one sequence generator. The query uses information_schema.schemata view in order to find initially all the schemas - the reason is that a schema might have only external sequence generators, only internal sequence generators, or both.
Type Sofware measure Numeric values (software measures) about the database
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH seq_stats AS (SELECT schema_name, Count(DISTINCT s.sequence_name) AS number_of_external_sequences, (SELECT Count(*) AS cnt FROM 
information_schema.columns AS c WHERE c.table_schema=sh.schema_name AND c.is_identity='YES') AS number_of_internal_sequences
FROM information_schema.schemata AS sh LEFT JOIN information_schema.sequences AS s
ON sh.schema_name=s.sequence_schema
WHERE (schema_name='public' OR
(schema_owner<>'postgres' AND schema_name IS NOT NULL))
GROUP BY schema_name)
SELECT schema_name, number_of_external_sequences, number_of_internal_sequences, number_of_external_sequences+number_of_internal_sequences AS total_number_of_sequences
FROM seq_stats
WHERE number_of_external_sequences+number_of_internal_sequences>0
ORDER BY schema_name;

Collections

This query belongs to the following collections:

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Find quick numeric overview of the database

Queries that return numeric values showing mostly the number of different types of database objects in the database

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Find quick numeric overview of the databaseQueries that return numeric values showing mostly the number of different types of database objects in the database

Categories

This query is classified under the following categories:

Comfortability of data management

Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.

Sequence generators

Queries of this category provide information about sequence generators and their usage.

NameDescription
Comfortability of data managementQueries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.