The Create Database Sequence and Delete Database Sequence tools help to aid in automated workflows where the sequence name is recorded and maintained. For example, when creating an attribute rule where a sequence is used as part of the script expression. You will keep track of the sequence name for these workflows to help reference them directly in your workflows.
What if you don't recall the name of the sequence that was created from one of your workflows, or you want to be able to inspect the properties of the existing sequences in the database?
Many of you have asked for the ability to view database sequences directly from ArcGIS Pro.
For FGDB data sources, you can use the ListDatabaseSequences function to get a list of database sequences. This allows you to confirm the sequence name and review sequence properties before creating or deleting database sequences in your geodatabase.
What about other data sources, like EGDB?
While there is no current tool or UI in ArcGIS Pro to view and manage database sequences for enterprise geodatabases, there are many options available.
View and manage database sequences:
- Use a database client application
For enterprise geodatabases there are many 3rd party client applications available to view and manage database sequences directly. Depending on your DBMS platform, the vendor may have a client application that is best suited for working with sequences. For example: SQL Server Management Studio.
Dbeaver is also a good multi-platform client application that can be used.
- Use a Python script
With a python script, you can use the ArcSDESQLExecute class to execute SQL statements against an enterprise geodatabase connection and print the sequence name and properties directly in the python window. You can find some example scripts below for specific platforms.
Python Script examples
SQL Server:
import arcpy
# Path to your SDE connection file
sde_conn = r"C:\Users\dak12273\AppData\Local\Temp\ArcGISProTemp29572\Untitled\SQLServer-DevDGD00100-SQLServerTesting.sde"
# SQL query to get sequence names and their schema owners
sql = """
SELECT
CAST(s.name AS varchar(128)) AS SequenceOwner,
CAST(seq.name AS varchar(128)) AS SequenceName,
ISNULL(CONVERT(varchar(100), seq.start_value), '') AS StartValue,
ISNULL(CONVERT(varchar(100), seq.increment), '') AS IncrementValue,
ISNULL(CONVERT(varchar(100), seq.current_value), '') AS CurrentValue
FROM sys.sequences AS seq
JOIN sys.schemas AS s ON seq.schema_id = s.schema_id
ORDER BY s.name, seq.name;
"""
ex = arcpy.ArcSDESQLExecute(sde_conn)
try:
rows = ex.execute(sql)
finally:
try: ex.close()
except: pass
if rows:
# Print header
print(f"{'SequenceOwner':<20}{'SequenceName':<35}{'Start':<10}{'Increment':<12}{'Current':<10}")
print("-" * 90)
# Print rows
for owner, name, start_v, inc_v, cur_v in rows:
print(f"{owner:<20}{name:<35}{start_v:<10}{inc_v:<12}{cur_v:<10}")
else:
print("No sequences found.")
Postgresql:
import arcpy
# Path to your SDE connection file
sde_conn = r"C:\\Users\\dak12273\\AppData\\Local\\Temp\\ArcGISProTemp29572\\Untitled\\PostgreSQL-DevDGD00100-pg_testing(dakota).sde"
# SQL query to get sequence names and their schema owners
sql = """
SELECT
sequence_name AS SequenceName,
start_value AS StartValue,
increment AS IncrementValue
FROM information_schema.sequences
ORDER BY sequence_name;
"""
ex = arcpy.ArcSDESQLExecute(sde_conn)
try:
rows = ex.execute(sql)
finally:
try: ex.close()
except: pass
if rows:
# Print header
print(f"{'SequenceName':<35}{'Increment':<12}{'StartValue':<10}")
print("-" * 90)
# Print rows
for name, start_v, inc_v in rows:
print(f"{name:<35}{inc_v:<12}{start_v:<10}")
else:
print("No sequences found.")
Oracle:
import arcpy
# Path to your SDE connection file
sde_conn = "path to your SDE connection file"
# SQL query to get sequence names and their schema owners
sql = """
SELECT
sequence_name AS SequenceName,
min_value AS StartValue,
increment_by AS IncrementValue,
last_number AS CurrentValue
FROM user_sequences
ORDER BY sequence_name;
"""
ex = arcpy.ArcSDESQLExecute(sde_conn)
try:
rows = ex.execute(sql)
finally:
try: ex.close()
except: pass
if rows:
# Print header
print(f"{'SequenceName':<35}{'StartValue':<10}{'Increment':<12}{'Current':<10}")
print("-" * 90)
# Print rows
for name, start_v, inc_v in rows:
print(f"{name:<35}{start_v:<10}{inc_v:<12}{cur_v:<10}")
else:
print("No sequences found.")
Mobile GDB:
import arcpy
import sqlite3
gdb_path = r"C:\Users\dak12273\AppData\Local\Temp\ArcGISProTemp29572\Untitled\Sequence.geodatabase"
conn = sqlite3.connect(gdb_path)
cur = conn.cursor()
cur.execute("""
SELECT
sequence_name AS SequenceName,
nextval AS NextValue,
increment AS IncrementValue,
starting_point AS StartValue
FROM GDB_Sequences
ORDER BY sequence_name;
""")
rows = cur.fetchall()
# headers
print(f"{'SequenceName':<35}{'NextValue':<12}{'Increment':<12}{'StartValue':<12}")
print("-" * 71)
for seq_name, next_val, inc, start_val in rows:
print(f"{seq_name:<35}{str(next_val):<12}{str(inc):<12}{str(start_val):<12}")
conn.close()
