The number of sequence generators in different schemas

Query 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 about the query:

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.

Query type:

Sofware measure (Numeric values (software measures) about the database)

Query license:

MIT License

Data source:

INFORMATION_SCHEMA only

SQL query:

Click on query to copy it

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 where the query belongs to

Collection name	Collection description
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

Collection name

Collection description

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

Categories where the query belongs to

Category name	Category description
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.

Category name

Category description

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.