Ability to view Database Sequence in ArcGIS Pro

[Brian_McLeer]

In creating database sequences, there is not a streamlined way to view database sequences that have been created. I have seen other posts of using Python to list database sequences, but that seems to only be applicable to file geodatabase and not enterprise databases. 

List Database Sequences for Enterprise Geodatabase 

Being able to view database sequences in a database would be beneficial, especially as these can interact with attribute rules to auto-generate values. Viewing along the same lines as you can for attribute rules, domains, subtypes, etc. 

[MatthewStull1]

I need to delete all the sequences in my Enterprise (SDE) geodatabase.  Can I simply go into SQL Server Management Studio and do the deletes there or should I just use the DeleteDatabaseSequence in Pro?

[Brian_McLeer]

@MatthewStull1 I always prefer to use geoprocessing tools before SQL, usually cleaner that way. Maybe you can use the batch version of the GP tool? 

[MatthewStull1]

I just ended up making a model in Model Builder and then exported it to Python.  Then I simply added additional lines in the code to do a batch delete of all my sequences.  It worked perfectly.  

I will say that I support the idea that Brian originally posted.  Having a view, like how we can see domains in Pro, to see all the sequences and be able to edit, add or delete them there would be really helpful!

[AKRRMapGuy]

Has anybody found a solution for Enterprise Geodatabses? The python tool only supports FGDBs... ESRI dropped the ball hard on this. Half a decade later and still no meaningful response from ESRI?

[Brian_McLeer]

@AKRRMapGuy there is still not a front end way to view through ArcGIS Pro, but in Microsoft SQL Server you can run the query below to get a list of database sequences created on your DB. 

SELECT * 
FROM sys.sequences;

[AKRRMapGuy]

@Brian_McLeer Thank you, you are a saint and a scholar. I just ran into a situation where an Attribute Rule was failing saying the sequence didn't exist in the database, but then when I try to make a sequence with that name in Pro it fails because that sequence already exists.... 😵 A tool in pro to diagnose this sure would've been useful.

[TomAP]

Have successfully used this Python script in Notebook in ArcGIS Pro to list Enterprise SQL database sequences and owners.

import arcpy

# Path to your SDE connection file
sde_conn = r"Q:\FolderPath\SDE.sde"

# SQL query to get sequence names and their schema owners
sql = """
SELECT
s.name AS SequenceOwner,
seq.name AS SequenceName
FROM
sys.sequences AS seq
JOIN
sys.schemas AS s ON seq.schema_id = s.schema_id
ORDER BY
s.name, seq.name;
"""

# Execute the query
executor = arcpy.ArcSDESQLExecute(sde_conn)
results = executor.execute(sql)

# Display results
if results:
print(f"{'Owner':<30}Sequence")
print("-" * 50)
for row in results:
print(f"{row[0]:<30}{row[1]}")
else:
print("No sequences found.")